Displaying latest data when data is equal to 0.

[08152023]

Hello, 

I am trying to pull the latest data (updated monthly) by calculating the MAX date where the "Actual" column is not blank. However, the issue arises when the latest data is equal to 0. PowerBI reads the latest data as the last row that does not contain 0. The following screenshot shows data through December 2023, with December = 0%. 

The current DAX formula: 

Comm_Actual =

    VAR DateActual =

        CALCULATE(MAX('Communications Append'[Date]), 'Communications Append'[Actual]<>BLANK(),  

    VAR RecentActual =

        CALCULATE(SELECTEDVALUE('Communications Append'[Actual]), 'Communications Append'[Date] = DateActual)

    Return

    IF(AND(ISBLANK(RecentActual),NOT(ISBLANK('Communications Append'[Comm_Target]))),0,RecentActual)

Is there a way to rewrite the DAX formula so it pulls the latest data, including when the latest data is equal to 0?

 

[amustafa · Accepted solution]

To address the issue where the latest data is equal to 0 and you want to consider it as the latest data instead of skipping it, you can modify your DAX formula to include zeros as well. The issue with your current formula is that it's using `SELECTEDVALUE` which will not consider a row with a 0 value as it is treated the same as BLANK by the filter `Actual <> BLANK()`.

Here's a revised version of the DAX formula that includes the 0 values as well:

Comm_Actual =
VAR MaxDateWithActual =
CALCULATE(MAX('Communications Append'[Date]), NOT(ISBLANK('Communications Append'[Actual])))
VAR RecentActual =
CALCULATE(SELECTEDVALUE('Communications Append'[Actual]), 'Communications Append'[Date] = MaxDateWithActual)
RETURN
IF(ISBLANK(RecentActual), 0, RecentActual)

In this version, `MaxDateWithActual` calculates the maximum date where the `Actual` column has any value, including 0. Then `RecentActual` gets the `Actual` value for that date. Finally, if `RecentActual` is BLANK (which shouldn't happen unless there's no data at all), it returns 0, otherwise, it returns the `Actual` value found.

Remember to replace `'Communications Append'` with the actual name of your table if it's different. This formula should work assuming that a 0 value is actually stored in the data and not just displayed as 0% while the underlying data is blank or null.

[08152023]

This worked perfectly! Thank you so much! This was a huge help!