Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Add % Diff from two years

Dear All, 

 

I need to add a DAX to show only the % change difference between 2 years where it always compares( Max Date / Min Date)-1

 

I've tried 2 things and it did return the change percent correctly, but I could not make it work for visualization chart. It returns 0 once added years in the column.  

 

First attempt: 

 

Diff from 2way total = IF([Total 2way]<>0,(DIVIDE(CALCULATE([Total 2way],FILTER('Calendar','Calendar'[Year]=MAX('Calendar'[Year]))), CALCULATE([Total 2way],FILTER('Calendar', 'Calendar'[Year]=MIN('Calendar'[Year]))))),0)-1

 

Second attempt: 

 

Max 2way = CALCULATE(SUM(ATM[2way AC]), FILTER('Calendar','Calendar'[Year]=MAX('Calendar'[Year])))

Min 2way = CALCULATE(SUM(ATM[2way AC]), FILTER('Calendar','Calendar'[Year]=MIN('Calendar'[Year])))

Diff in % = IF([Max 2way]<>0,(DIVIDE([Max 2way],[Min 2way],0)-1))

 

 

If added measure it will calculate both 2017 and 2018, but not the difference

 

 

Here the Diff % value is correct but I have to add both [Max 2way] and [Min 2way] in the column value. 

 

Thank you, 

 

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable

      Thanks, Greg 

       

      Could you please explain where should I use "ALL"? 

       

       

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee

        Hi Anonymous,

         

        It could be like below.

        Max 2way =
        CALCULATE (
            SUM ( ATM[2way AC] ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = MAX ( 'Calendar'[Year] ) )
        )
        
        Min 2way =
        CALCULATE (
            SUM ( ATM[2way AC] ),
            FILTER ( ALL ( 'Calendar' ), 'Calendar'[Year] = MIN ( 'Calendar'[Year] ) )
        )
        

        Best Regards,

        Dale

  • v-jiascu-msft's avatar
    v-jiascu-msft
    Microsoft Employee

    Hi Anonymous,

     

    Could you please mark the proper answers as solutions?

     

    Best Regards,

    Dale