DAX

Dax Function: LASTNONBLANK

Category: Filter Functions

The LASTNONBLANK function in Power BI is a DAX function used to return the last value in a column that is not blank, considering the current filter context. It evaluates the column and returns the last non-blank value along with any expression provided.

Purpose

Identify the last meaningful data point: Often used in time-series analysis to find the most recent non-blank value.
Support calculations with non-continuous data: Helps avoid errors by ignoring blank values in calculations.
Dynamic evaluations: Adapt to context and filter changes.

Type of Calculations

Evaluative: Identifies the last row with a valid (non-blank) entry.
Aggregate-Compatible: Often combined with aggregation functions for additional insights.

Practical Use Cases

Time-Series Analysis: Find the most recent value in a dataset for reporting trends or ending balances.
Dynamic Metrics: Show the latest available metric in dashboards, e.g., last recorded sales.
Filter Validation: Ensure filters are applied to meaningful data and ignore blanks.


LASTNONBLANK(<column>, <expression>)</expression></column>

Parameter	Type	Description
`Column`	Column	The column in which to find the last non-blank value.
`Expression`	Expression	An expression to evaluate for the rows where the column value is non-blank. It can be a calculation.

How Does LASTNONBLANK Dax Works

Evaluate Non-Blank Rows: The function scans the column to locate non-blank rows.
Context Sensitivity: Considers the current filter context to determine applicable rows.
Expression Evaluation: Evaluates the provided expression for each non-blank row.
Return Last Value: Retrieves the last row’s value in the column where the expression is valid.

Logical Flow

Filter blank values.
Apply the expression on non-blank rows.
Return the value from the last matching row.

What Does It Return?

Scalar Value: The last non-blank value in the column, based on the evaluation of the given expression.

When Should We Use It?

To display the most recent non-blank data in reports or visuals.
As part of measures for cumulative calculations where the latest value is needed.
In scenarios requiring blank values to be excluded for accurate results.

Examples

Basic Usage :


Last Sale Date =
LASTNONBLANK(Sales[Date], 1)

Returns the most recent non-blank date in the Sales table.

Column Usage


Last Recorded Amount =
LASTNONBLANK(Sales[Amount], Sales[Amount])

Finds the last recorded sales amount in the Sales table.

Advanced Usage


Ending Balance =
CALCULATE(
SUM(Sales[Balance]),
LASTNONBLANK(Sales[Date], SUM(Sales[Balance]))
)

Calculates the ending balance for the most recent date with data.

Tips and Tricks

Combine with CALCULATE: Use with CALCULATE to modify or refine the filter context.
Avoid Overuse: Limit its use in datasets with large gaps of blank values to maintain performance.
Test Filters: Ensure filters are set correctly to retrieve meaningful results.

Performance Impact of LASTNONBLANK DAX Function:

Efficiency: Works best in datasets with minimal blank values or targeted filters.
Indexing: Ensure the column is properly indexed to optimize performance in large datasets.

Related Functions You Might Need

FIRSTNONBLANK: Similar but retrieves the first non-blank value.
LASTDATE: Returns the last date in the context.
REMOVEFILTERS: Useful for resetting filters before using LASTNONBLANK.

Want to Learn More?
For more information, check out the official Microsoft documentation for LASTNONBLANK You can also experiment with this function in your Power BI reports to explore its capabilities.

Unlock the full capabilities of Power BI and elevate your data insights with our specialized consulting services. Whether you need guidance on advanced DAX functions like those highlighted here, support in designing interactive dashboards, or expertise in optimizing data models for enhanced performance, our experienced Power BI consultants are equipped to deliver customized solutions for your business. Explore our Power BI Consulting Services page to discover how we can help your organization make smarter, data-driven decisions.

1. What is the primary use of LASTNONBLANK?

It is used to retrieve the last non-blank value in a column based on the current filter context.

2. How does LASTNONBLANK handle blank values?

It skips all blank values and evaluates the last valid entry.

3. Can I use LASTNONBLANK without an expression?

Yes, you can use a simple constant or a column reference as the expression.

4. Is LASTNONBLANK case-sensitive?

The function itself is not case-sensitive, but the underlying data might be, depending on the source.

5. How does LASTNONBLANK differ from FIRSTNONBLANK?

LASTNONBLANK retrieves the last non-blank value, whereas FIRSTNONBLANK retrieves the first non-blank value.

Dax Function: KEEPFILTERS

Dax Function: LASTNONBLANKVALUE