Forum Discussion

Oscar_Mtz_V's avatar
Oscar_Mtz_V
Kudo Commander
6 years ago

Previous non-continuous date value

I have a table from where I want to get the value from the previous available date for the specific Country-Indicator. The previous available date can be a day, month or quarter before. I am a...
DAX
time functions
jameszhang0805's avatar
jameszhang0805
Resolver IV
5 years ago

Oscar_Mtz_V 
If the below screenshot is your expected result , please try the below code, you don't need to combine the Country and Indicator as a key, don't need to add an index column in Power Query. Just use DAX code.

PreviPreviousValue = 
VAR _CurrentDate =
    SELECTEDVALUE ( Sales[Date] )
VAR _PreviousDate =
    CALCULATE (
        MAX ( 'Sales'[Date] ),
        ALLEXCEPT ( Sales, Sales[Country], Sales[Indicator] ),
        KEEPFILTERS ( 'Sales'[Date] < _CurrentDate )
    )
VAR _Result =
    CALCULATE (
        SUM ( Sales[Value_Rev] ),
        'Sales'[Date] = _PreviousDate,
        REMOVEFILTERS ( Sales[Date] )
    )
RETURN
    _Result