cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
smortil Frequent Visitor
Frequent Visitor

DAX - Difference between min and max year

Hello Power BI community,

 

I need help coming up with a DAX formula to calculate the difference between the minimum (lowest selected) fiscal year and the maximum (highest selected) fiscal year.

To summarize, I would like to subtract the lowest selected fiscal year from the highest selected fiscal year. In the screencapture below, the rate should read as -1% (4% in 2017/2018 MINUS 5% in 2014/2015) .Capture.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

Here is what I have so far:

Min and Max Year Rate Change =

CALCULATE(

 SUM('Master'[Rate]),

 FILTER('Master','Master'[Fiscal Year]=MAX('Master'[Fiscal Year])

 )

 ),

-

 CALCULATE(

 SUM('Master'[Rate]),

 FILTER('Master','Master'[Fiscal Year]=MIN('Master'[Fiscal Year])))

,0)

 

The expression above yields the following error message:

To use special characters in a measure name, enclose the entire name in brackets ( [] ) and add a ] to any closing brackets in the name.

 

Sample workbook with altered data below for reference:

Fiscal YearRate
2006/20079.1%
2007/20085.6%
2008/20093.7%
2009/201016.9%
2010/201116.4%
2011/201215.3%
2012/20138.9%
2013/20148.9%
2014/20154.6%
2015/201616.7%
2016/201715.1%
2017/20182.1%
2018/201910.6%

 

Any help on the matter would be greatly appreciated Smiley Happy thank you!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: DAX - Difference between min and max year

Didn't dig into what is potentially wrong with your formula, and I got a positive 2.5 with the data you supplied. 4.6 - 2.1

 

Measure 2 = 
VAR __max = MAX('Table5'[Fiscal Year])
VAR __min = MIN('Table5'[Fiscal Year])
RETURN
SUMX(FILTER('Table5',[Fiscal Year]=__min),[Rate]) - SUMX(FILTER('Table5',[Fiscal Year]=__max),[Rate])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


2 REPLIES 2
Super User
Super User

Re: DAX - Difference between min and max year

Didn't dig into what is potentially wrong with your formula, and I got a positive 2.5 with the data you supplied. 4.6 - 2.1

 

Measure 2 = 
VAR __max = MAX('Table5'[Fiscal Year])
VAR __min = MIN('Table5'[Fiscal Year])
RETURN
SUMX(FILTER('Table5',[Fiscal Year]=__min),[Rate]) - SUMX(FILTER('Table5',[Fiscal Year]=__max),[Rate])

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


smortil Frequent Visitor
Frequent Visitor

Re: DAX - Difference between min and max year

Thank you so much! This works well for me.