Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Solved! Go to 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
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
Hi @Anonymous,
Could you please mark the proper answers as solutions?
Best Regards,
Dale
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
Thanks, Greg
Could you please explain where should I use "ALL"?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |