cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper III
Helper III

% Difference between MIN and MAX date

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 )

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper III
Helper III

Re: % Difference between MIN and MAX date

@V-lianl-msft, @amitchandak

Found a solution, sadly not sure how I got there, but here is what I did. I created new measures:

 

First Date ACV = CALCULATE([ACV], FILTER( 'Divya ACV', 'Divya ACV'[Year] = [First Date]))
 
where
 
First Date = CALCULATE ( MIN ('Divya ACV'[Year]), ALLSELECTED( ( 'Divya ACV' )))
 
and
 
ACV % Change = DIVIDE('ACV'[Change in ACV], 'ACV'[First Date ACV])

View solution in original post

6 REPLIES 6
Highlighted
Super User IV
Super User IV

Re: % Difference between MIN and MAX date

@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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper III
Helper III

Re: % Difference between MIN and MAX date

@amitchandak

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:

 
 
 
 
 
 
YearACVMINMAX
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

Highlighted
Microsoft
Microsoft

Re: % Difference between MIN and MAX date

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.

Highlighted
Helper III
Helper III

Re: % Difference between MIN and MAX date

@V-lianl-msft , what is the calculation that I should be using to calculate the % difference between the min and max date? 

Highlighted
Helper III
Helper III

Re: % Difference between MIN and MAX date

@V-lianl-msft, @amitchandak

Found a solution, sadly not sure how I got there, but here is what I did. I created new measures:

 

First Date ACV = CALCULATE([ACV], FILTER( 'Divya ACV', 'Divya ACV'[Year] = [First Date]))
 
where
 
First Date = CALCULATE ( MIN ('Divya ACV'[Year]), ALLSELECTED( ( 'Divya ACV' )))
 
and
 
ACV % Change = DIVIDE('ACV'[Change in ACV], 'ACV'[First Date ACV])

View solution in original post

Highlighted
Microsoft
Microsoft

Re: % Difference between MIN and MAX date

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.

Helpful resources

Announcements
Super Users of the Quarter - Q2 2020

Super Users of the Quarter - Q2 2020

Who are our Super User Superstars? Who made it to the top of the leaderboards? Get the answers!

June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors