topic Re: Percentage Change DAX formula in DAX Commands and Tips

Percentage Change DAX formula

Nick221_ — Wed, 29 Jan 2025 16:54:07 GMT

I have two measures "Start Month" and "End Month" in two cards. Below is the formula for these measures.

I have added two individual slicers by Month. Is it possible to calculate the percentage change of these two filtered measures, so that the formula will update depending on which month is selected from each?

The table that is being filtered is titled "Date" filtered by column "Month".

Re: Percentage Change DAX formula

mark_endicott — Wed, 29 Jan 2025 17:19:20 GMT

Nick221_ - try this:

VAR _change = [EndMonth] - [StartMonth] RETURN DIVIDE( _change, [StartMonth] )

If this works, please mark it as he solution for others with the same challenge.

Re: Percentage Change DAX formula

Nick221_ — Wed, 29 Jan 2025 19:07:44 GMT

what does the _change represent in the divide calculation? I receive the below error.

Re: Percentage Change DAX formula

Nick221_ — Wed, 29 Jan 2025 19:33:09 GMT

mark_endicott

Re: Percentage Change DAX formula

Anonymous — Thu, 30 Jan 2025 07:10:37 GMT

Hi Nick221_

This is a syntax error, you need to give the measure a name.

For example,

Measure = VAR _change = [EndMonth] - [StartMonth] RETURN DIVIDE( _change, [StartMonth] )

The _change is a variable to store the value of the [EndMonth] - [StartMonth].

Hope this can help.

Best Regards

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

Re: Percentage Change DAX formula

mark_endicott — Thu, 30 Jan 2025 10:29:34 GMT

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.