Forum Discussion
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,
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
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
4 Replies
- Greg_DecklerCommunity Champion
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
- AnonymousNot applicable
Thanks, Greg
Could you please explain where should I use "ALL"?
- v-jiascu-msftMicrosoft 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-msftMicrosoft Employee
Hi Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale