topic Return prior value if no value exists for current Month/Year in DAX Commands and Tips

Return prior value if no value exists for current Month/Year

robertpayne21 — Mon, 17 Jul 2023 23:21:35 GMT

Hi, I'm having trouble with some dax. This is working as expected for a month with values and returning the prior date. If the current Month/Year does not have a value, it will return the prior month/year. What I am having trouble with is returning the value for that period. For example, May 2023 has no value but we can see that April 2023 has a value. I would like to display the April 2023 value or the previous value until there is a value with a date that is >= selected date.

_TEST1 Dated Avg Labor Rate =

var _minsel = MAX(DimDate[Date])

var _selected = LASTNONBLANK(FILTER(ALL(DimDate[Date]), DimDate[Date]<=_minsel),[Hmmm])

Return _selected

Not seeing April value forward.

April Value

For reference:

Hmmm = sum(VW_SPECIAL_CUSTOMER_LABOR_RATE_UNIONED_VIEWS[LABOR_RATE])

Re: Return prior value if no value exists for current Month/Year

johnt75 — Tue, 18 Jul 2023 09:34:00 GMT

You can modify your code which returns the date to use LASTNONBLANKVALUE instead of LASTNONBLANK.

Re: Return prior value if no value exists for current Month/Year

robertpayne21 — Wed, 19 Jul 2023 13:43:20 GMT

This did work with one caveat. On the report, the date filter needs to use the full date key, for example dimdate[date] vs dimdate[monthyear]. Thank You.