Year over Year calculation default to value with latest date in the data set

[Anonymous]

Hi All,

I've searched high and low and can't find an answer to this question. I need to add a card to my dashboard that shows the YOY calculation. I used the quick measure option and it works like a charm. My issue though, is that I want the card to default to the value for the latest date in the data set (before any date is selected in the slicer). 

In my example, I have dates going from 1/1/2020 through 1/1/2022. The card should show the YOY Diff value, based on the date selected in the slicer. As the dates are selected in the date slicer, the values are correct in the card. But as a default, I want the card to show the 1/1/2022 value of 122%. (the latest date in the dataset). Next month, the default value will be the 2/1/2022 value.  I don't want to set a filter on the card because it needs to be dynamic. Can anyone help me please? 

Attached is a screenshot of the data set, card and slicer.  Much appreciated!!

Tinar1029

 

[AllisonKennedy · Accepted solution]

@Anonymous  You can use the LASTDATE function to find the most recent transaction in your fact table. 

So add an IF condition to your measure:

= 

VAR _LastTransaction = LASTDATE ( TblSampleData[Date] )

VAR _SelectedDate = MAX( DateDim[Date] )

RETURN

IF ( _SelectedDate < _LastTransaction, [YoY Measure], CALCULATE ( [YoY Measure] , DateDim[Date] = _LastTransaction ) )