Forum Discussion
Percentage Change DAX formula
what does the _change represent in the divide calculation? I receive the below error.
Nick221_ - It's a variable (VAR) that stores a value. A.K.A we compute the difference or change between the start month & end month, store it and use it later in the DIVIDE.
There are two issues with the code now:
1) You have not kept the name of the measure, you have started with my variable
2) I did not notice that you have a space in [End Month], but no space in [StartMonth] so the syntax error is also because this measure cannot find the [EndMonth] measure (it's actually called [End Month]).
This shoudl fix your issue:
Pct Change =
VAR _change = [End Month] - [StartMonth]
RETURN
DIVIDE( _change, [StartMonth] )
Here's some more info: Variables are used in DAX to help with optimisation by storing values so that they do not need to be computed later in the calculation and to help with readability. In actual fact the most optimal version of the code I have given you is:
Pct Change =
VAR _start = [StartMonth]
VAR _change = [End Month] - _start
RETURN
DIVIDE( _change, _start )
This means that [Start Month] is only calculated once and stored, whereas in my earlier version it was calculated twice. I think this 2nd version is less readable however, and the difference it will make to the performance will be negligable.
You could write this calculation without variables (VAR) but this would be harder to read and understand at a glance:
Pct Change =
DIVIDE( [End Month] - [StartMonth] , [StartMonth] )
I hope this now works, and provides some information on variables within DAX. If this has solved your issue, please accept it as the solution, so others with the same challenge can find the information.