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:
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:
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)
Power BI DAX Intensive Training Course£1,050.00 – £26,550.00
Power BI DAX Introduction£395.00 – £9,750.00
Power BI DAX Intermediate£395.00 – £9,750.00
Power BI DAX Advanced£395.00 – £9,750.00