Forum Discussion

VJ89's avatar
VJ89
Regular Visitor
9 years ago
Solved

DAX previous month value

Hi All,   I have a scenario where i am supposed to get the previous month measure of the top scorer in the current month. For example. my data look like below     name Date Score measure ...
  • LaurentCouartou's avatar
    9 years ago

    My suggestion:

     

    Previous month score for top scorer = 
    VAR TopScorers = TOPN( 1, VALUES( Scores[name] ), CALCULATE(AVERAGE(Scores[Score])) ) VAR PrevMonth = CALCULATETABLE( DATEADD(Scores[Date],-1, MONTH), LASTDATE( Scores[Date]) ) RETURN CALCULATE( AVERAGE(Scores[Score]) , TopScorers , PrevMonth )

     

    Note the TOPN function wil return several players if you have ties in your current selection. Hence the average calculation.

     

    Also, in case several months/dates are selected, I take the last one and calculate the previous month for this date.