Replace repeated SUM calculation with VAR

[Anonymous]

Hello,

I have the following measure to workout the difference between a value in month and the previous month:

status_change=

VAR Prior_month =

    CALCULATE ( SUM('Table'[Value]), PARALLELPERIOD ( 'Dates'[Date], -1, MONTH ) )

VAR difference = ( Prior_month - SUM ( 'Table'[Value] ) )

Return

difference

I have tryed to replace the 'SUM('Table'[Value])' with a Variable at the start (as below) but it does not return the expected results. Would anyone be able to tell me why this is the case? Also is there a better method for working out the difference?

status_change=

VAR this_month = SUM('Table'[Value])

VAR Prior_month =

    CALCULATE ( this_month, PARALLELPERIOD ( 'Dates'[Date], -1, MONTH ) )

VAR difference = this_month - Prior_month

Return

difference

[Bubble4502 · Accepted solution]

Hi @Anonymous ,

Approve with @amitchandak , the variables are evaluated once in the code or calculation for each context. This means that the result of the measure won't change if we manipulate the filter context in the expression later in the code. Due to this reason, the result of 

CALCULATE ( SUM('Table'[Value]), PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) ) is different from 

CALCULATE ( this_month, PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) )

For example:

I have a table like this

Here are the two measures:

Prior Month without var = 
VAR Prior_month =
    CALCULATE ( SUM('Table'[Value]), PARALLELPERIOD ('Date'[Date], -1, MONTH ) )
Return
Prior_month

Prior Month with var = 
VAR this_month = SUM('Table'[Value])
VAR Prior_month =
    CALCULATE ( this_month, PARALLELPERIOD ( 'Date'[Date], -1, MONTH ) )
Return Prior_month

Output:

Kind Regards,

Bubble

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Anonymous ,

https://community.powerbi.com/t5/Community-Blog/DAX-Why-and-how-to-use-variables/ba-p/2351817

https://towardsdatascience.com/three-things-you-need-to-know-when-using-variables-in-dax-c67724862b57