Add % Diff from two years

[Anonymous]

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, 

[Greg_Deckler · Accepted solution]

You could solve that last issue through the use of ALL. 

Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[v-jiascu-msft]

Hi @Anonymous,

Could you please mark the proper answers as solutions?

Best Regards,

Dale

[Greg_Deckler · Accepted solution]

You could solve that last issue through the use of ALL. 

Sample data would help tremendously. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

[Anonymous]

Thanks, Greg 

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

[v-jiascu-msft · Accepted solution]

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