0%

Working with dates/datetime or string/text in the grid

When working with date/datetime or string/text values in the accoPLANNING grid, it is necessary to explicitly define how the latest updated row should be determined.

By default, Power BI applies standard aggregations such as SUM, MAX, or MIN when multiple rows exist. This behavior is not suitable when the requirement is to display a single value from the most recently updated record. Therefore, a custom DAX measure must be created to control which value is returned.

In most planning and writeback scenarios, the desired behavior is to retrieve the value from the latest updated row, based on an update timestamp.

To achieve this, you can create a DAX measure using the LASTNONBLANK function. This function allows you to identify the most recent update date while maintaining full control over which value is returned.

Why LASTNONBLANK May Exclude the Latest Row

When using the following pattern:

LASTNONBLANK ( Table[Updatedate], MAX ( Table[Date] ) )

the function:

  • Iterates over Updatedate

  • Evaluates MAX ( Date ) per update timestamp

  • Skips rows where Date is BLANK

As a result, the true last updated row may be ignored if its Date value is BLANK.

The example below is designed to return the value from the latest updated row, even if that value is BLANK (null). This ensures that the true last update is respected and not skipped due to missing data.

Last Entered Date :=
VAR LastUpdateDate =
    LASTNONBLANK (
        vPOC_Facttable_Finance_total[Updatedate],
        1
    )
RETURN
    CALCULATE (
        SELECTEDVALUE ( vPOC_Facttable_Finance_total[Date] ),
        vPOC_Facttable_Finance_total[Updatedate] = LastUpdateDate

Why this works

  • LASTNONBLANK( Updatedate, 1 )

    • Uses a constant expression (1) that is never BLANK

    • Therefore returns the latest Updatedate, even if all other columns are BLANK

  • SELECTEDVALUE(Date)

    • Returns the Date from the last updated row

    • Will correctly return BLANK() if the value is null

This matches the business definition of:

“Return the Date from the most recently updated record, even if that Date is empty.”

0%