Forum Discussion

robertpayne21's avatar
robertpayne21
Frequent Visitor
3 years ago
Solved

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

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])
 
 
 

 

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

2 Replies

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

    • robertpayne21's avatar
      robertpayne21
      Frequent Visitor

      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.