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 ...
Need Help
  • LaurentCouartou's avatar
    LaurentCouartou
    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.

LaurentCouartou's avatar
LaurentCouartou
Solution Supplier
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.

VJ89's avatar
VJ89
Regular Visitor
9 years ago

Hey Thanks a lots, it worked like charm!! :)