Forum Discussion

Nick221_'s avatar
Nick221_
Frequent Visitor
1 year ago

Percentage Change DAX formula

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".

 

 

5 Replies

  • mark_endicott's avatar
    mark_endicott
    Icon for Super User rankSuper User
    1 year ago

    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.

  • Nick221_'s avatar
    Nick221_
    Frequent Visitor
    1 year ago

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

     

    • mark_endicott's avatar
      mark_endicott
      Icon for Super User rankSuper User
      1 year ago

      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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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.