DAX - Difference between min and max year

[Anonymous]

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) .

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 Year	Rate
2006/2007	9.1%
2007/2008	5.6%
2008/2009	3.7%
2009/2010	16.9%
2010/2011	16.4%
2011/2012	15.3%
2012/2013	8.9%
2013/2014	8.9%
2014/2015	4.6%
2015/2016	16.7%
2016/2017	15.1%
2017/2018	2.1%
2018/2019	10.6%

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

[Greg_Deckler · Accepted solution]

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

[Anonymous]

Thank you so much! This works well for me.