cancel
Showing results for
Did you mean:
smortil 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) . 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!

1 ACCEPTED SOLUTION

Accepted Solutions 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

## 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!

Highlighted
smortil Frequent Visitor

## Re: DAX - Difference between min and max year

Thank you so much! This works well for me.