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.
Looking for help with % Change. A little background, I subtracted the ACV from the first year (MIN DATE) from the last year (MAX DATE) , so not a simple “let’s add up the sales” calculation. I ran into problems calculating the % difference.
When I plugged "MIN" into my first measure, the result comes up blank. Not sure what I am missing here. The next two measures are working pristinely, so I am assuming I oversimplified the ending of my first measure. I have already calculated the difference between the two dates so I'm able to simplify part of the ACV% Change calc.
Here are my three measures:
ACV % Change = DIVIDE([Change in ACV] , [MIN])
Change in ACV =
VAR __max = MAXx('Calendar Table','Calendar Table'[Year])
VAR __min = MINx('Calendar Table','Calendar Table'[Year])
RETURN
CALCULATE(SUM('Divya ACV'[Product Value]), FILTER(all('Calendar Table'), 'Calendar Table'[Year] = __max)) -CALCULATE(SUM('Divya ACV'[Product Value]), FILTER(all('Calendar Table'), ('Calendar Table'[Year]) =__min))
MIN =
VAR mind =
CALCULATE ( MIN ( 'Divya ACV'[Year] ), ALLSELECTED ( 'Divya ACV'[Year] ) )
RETURN
CALCULATE ( SELECTEDVALUE ( 'Divya ACV'[Product Value] ), 'Divya ACV'[Year] = mind )
Solved! Go to Solution.
Found a solution, sadly not sure how I got there, but here is what I did. I created new measures:
@cbtekrony , what exactly, you are trying to achieve here. The Min calc is wrong. What calculation you want min. What you want to display at the end
Thank you. I am trying to calculate the % change between the maximum year and the minimum year. When I ran my MIN Calc through my table, I get the correct answer, so I think my ACV % Change DAX is wrong. Here is minimum calc in action:
Year | ACV | MIN | MAX |
12/31/2011 | $1,397,633.70 | $6,553,524.60 | |
12/31/2012 | $1,397,633.70 | $1,397,633.70 | $6,553,524.60 |
12/31/2013 | $2,472,531.60 | $1,397,633.70 | $6,553,524.60 |
12/31/2014 | $4,010,372.20 | $1,397,633.70 | $6,553,524.60 |
12/31/2015 | $5,295,672.70 | $1,397,633.70 | $6,553,524.60 |
12/31/2016 | $6,553,524.60 | $1,397,633.70 | $6,553,524.60 |
Ultimately I want it to calculate ($6,553,524.60 - $1,397,633.70)/$1,397,633.70
Hi @cbtekrony ,
Calculate "_max" and "_min" at "change in ACV" as you did before at "MIN"
VAR mind =
CALCULATE ( MIN ( 'Divya ACV'[Year] ), ALLSELECTED ( 'Divya ACV'[Year] ) )
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@V-lianl-msft , what is the calculation that I should be using to calculate the % difference between the min and max date?
Found a solution, sadly not sure how I got there, but here is what I did. I created new measures:
Hi @cbtekrony ,
It's glad that you have solved your problem.
Please accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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 |
---|---|
117 | |
104 | |
77 | |
73 | |
52 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |