LASTNONBLANK

How to Use the Power BI DAX function LASTNONBLANK

What is the LASTNONBLANK Function?

The LASTNONBLANK function is a DAX function that returns the last non-blank value in a column or table. It takes two arguments:

– The first argument is the column or table to evaluate.

– The second argument is the expression to evaluate.

The expression is evaluated for each row in the column or table, and the last non-blank value is returned.

How to Use the LASTNONBLANK Function

To use the LASTNONBLANK function in Power BI, you need to follow these steps:

1. Open your Power BI report and go to the “Modeling” tab.

2. Click on “New Measure” to create a new measure.

3. In the formula bar, type the following formula:


LASTNONBLANK(Column/TableName, Expression)


4. Replace "Column/TableName" with the column or table you want to evaluate.

5. Replace "Expression" with the expression you want to evaluate.

For example, let's say you have a table called "Sales" with a column called "Revenue". You want to return the last non-blank value in the "Revenue" column for each product. You can use the following formula:


LASTNONBLANK(Sales[Revenue], Sales[Product])


This formula will evaluate the "Revenue" column for each row in the "Sales" table, and return the last non-blank value for each product.

Using LASTNONBLANK with Other DAX Functions

The LASTNONBLANK function can be used in combination with other DAX functions to create more complex calculations. For example, you can use it with the CALCULATE function to filter the data based on a certain condition. Let's say you want to calculate the total revenue for the last month. You can use the following formula:


CALCULATE(SUM(Sales[Revenue]), LASTNONBLANK(Sales[Date], Sales[Revenue]))


This formula will calculate the sum of the "Revenue" column, filtered by the last non-blank value in the "Date" column.

The LASTNONBLANK function is a powerful DAX function that can be used in a variety of scenarios. It allows you to return the last non-blank value in a column or table, which can be very useful in certain calculations. By using it in combination with other DAX functions, you can create complex formulas and calculations that will help you gain insights from your data.

Power BI DAX Training Courses by G Com Solutions (0800 998 9248)

Upcoming Courses

11-13 November 2024

Contact Us

    Subject

    Your Name (required)

    Company/Organisation

    Email (required)

    Telephone

    Training Course(s)

    Your Message

    Upload Example Document(s) (Zip multiple files)