Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
cbtekrony
Resolver I
Resolver I

% 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

@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
amitchandak
Super User
Super User

@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

@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

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? 

@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])

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.