cancel
Showing results for
Did you mean:
Highlighted
Helper I

## Calculate multi-year CAGR while ignoring slicer filter for date

I am looking to create a 5-year CAGR measure that is not impacted by the date year slicer that filters all other data in the table to one specific year. Example:  2016-2020 CAGR, but all other data in the table should show only calculations for 2020 based on the slicer filter.

Formulas I am using to calculate CAGR earliest year in data set is 2016 and latest year is 2020 = start and end periods of CAGR:

1 Beginning Value = CALCULATE(SUM(table[revenue]),FILTER(table,table[year]=MIN(table[year])))

1 Ending Value = CALCULATE(SUM(table[revenue]),FILTER(table,table[year]=MAX(table[year]

1 # of Years = (MAX(table[year])-MIN(table[year]))

1 CAGR = IFERROR(([1 Ending Value]/[1 Beginning Value])^(1/([1 # of Years])-1),0)

Please advise as to how to not filter the beginning and ending value formulas based on the one year slicer in the table.

Thank You

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: RicRe: Calculate multi-year CAGR while ignoring slicer filter for date

I build a new table to have a test.

Industry_Revenue Table:

Then I build a new measure:

``````Cagr_Industry =
VAR _BeginningValue =
CALCULATE (
SUM ( 'Industry_Revenue'[Revenue] ),
FILTER (
ALL ( 'Industry_Revenue' ),
'Industry_Revenue'[year]
= MINX ( ALL ( 'Industry_Revenue' ), 'Industry_Revenue'[year] )
&& Industry_Revenue[Industry] = MAX ( Industry_Revenue[Industry] )
)
)
VAR _EndingValue =
CALCULATE (
SUM ( 'Industry_Revenue'[Revenue] ),
FILTER (
ALL ( 'Industry_Revenue' ),
'Industry_Revenue'[year]
= MAXX ( ALL ( 'Industry_Revenue' ), 'Industry_Revenue'[year] )
&& Industry_Revenue[Industry] = MAX ( Industry_Revenue[Industry] )
)
)
VAR _MinYear =
MINX (
FILTER (
ALL ( 'Industry_Revenue' ),
Industry_Revenue[Industry] = MAX ( Industry_Revenue[Industry] )
),
'Industry_Revenue'[year]
)
VAR _MaxYear =
MAXX (
FILTER (
ALL ( 'Industry_Revenue' ),
Industry_Revenue[Industry] = MAX ( Industry_Revenue[Industry] )
),
'Industry_Revenue'[year]
)
VAR _ofYears =
DATEDIFF ( _MinYear, _MaxYear, YEAR )
RETURN
IFERROR ( ( _EndingValue / _BeginningValue ) ^ ( 1 / ( _ofYears - 1 ) ), 0 )``````

Result:

Default:

Select 2018 in Slicer:

You can download the pbix file from this link: Calculate multi-year CAGR while ignoring slicer filter for date

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 REPLIES 5
Highlighted
Super User V

## Re: Calculate multi-year CAGR while ignoring slicer filter for date

Hi,

Does this work?

CALCULATE(SUM(table[revenue]),FILTER(ALL(table),table[year]=MIN(table[year])))

Regards,
Ashish Mathur
http://www.ashishmathur.com
Highlighted
Microsoft

## Re: Calculate multi-year CAGR while ignoring slicer filter for date

Just like  Ashish_Mathur ’s  reply, we can use all function to achieve your goal.

I build a table as below to have a test.

Build a slicer, a table visual and a Cluster column visual.

Then we caluculate the cagr by measure.

``````Cagr =

VAR _BeginningValue =

CALCULATE (

SUM ( 'Table'[Revenue] ),

FILTER (

ALL ( 'Table' ),

'Table'[year] = MINX ( ALL ( 'Table' ), 'Table'[year] )

)

)

VAR _EndingValue =

CALCULATE (

SUM ( 'Table'[Revenue] ),

FILTER (

ALL ( 'Table' ),

'Table'[year] = MAXX ( ALL ( 'Table' ), 'Table'[year] )

)

)

VAR _ofYears =

DATEDIFF (

MINX ( ALL ( 'Table' ), 'Table'[year] ),

MAXX ( ALL ( 'Table' ), 'Table'[year] ),

YEAR

)

RETURN

IFERROR ( ( _EndingValue / _BeginningValue ) ^ ( 1 / ( _ofYears - 1 ) ), 0 )``````

Result:

Select 2018:

Our measure wont change by the slicer.

You can download the pbix file from this link: Calculate multi-year CAGR while ignoring slicer filter for date

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Helper I

## RicRe: Calculate multi-year CAGR while ignoring slicer filter for date

Hi @RicoZhou - your solution works perfectly on the overall level!  However, when I drop the CAGR measure in a table, it does not update based on the catregories in each row.  Example:  I have industries in the first column, each row is a different industry.  I would like to tweak your formula to provide the CAGR for each industry in the table row. Can you please provide guidance?

Thank You

Highlighted
Microsoft

## Re: RicRe: Calculate multi-year CAGR while ignoring slicer filter for date

I build a new table to have a test.

Industry_Revenue Table:

Then I build a new measure:

``````Cagr_Industry =
VAR _BeginningValue =
CALCULATE (
SUM ( 'Industry_Revenue'[Revenue] ),
FILTER (
ALL ( 'Industry_Revenue' ),
'Industry_Revenue'[year]
= MINX ( ALL ( 'Industry_Revenue' ), 'Industry_Revenue'[year] )
&& Industry_Revenue[Industry] = MAX ( Industry_Revenue[Industry] )
)
)
VAR _EndingValue =
CALCULATE (
SUM ( 'Industry_Revenue'[Revenue] ),
FILTER (
ALL ( 'Industry_Revenue' ),
'Industry_Revenue'[year]
= MAXX ( ALL ( 'Industry_Revenue' ), 'Industry_Revenue'[year] )
&& Industry_Revenue[Industry] = MAX ( Industry_Revenue[Industry] )
)
)
VAR _MinYear =
MINX (
FILTER (
ALL ( 'Industry_Revenue' ),
Industry_Revenue[Industry] = MAX ( Industry_Revenue[Industry] )
),
'Industry_Revenue'[year]
)
VAR _MaxYear =
MAXX (
FILTER (
ALL ( 'Industry_Revenue' ),
Industry_Revenue[Industry] = MAX ( Industry_Revenue[Industry] )
),
'Industry_Revenue'[year]
)
VAR _ofYears =
DATEDIFF ( _MinYear, _MaxYear, YEAR )
RETURN
IFERROR ( ( _EndingValue / _BeginningValue ) ^ ( 1 / ( _ofYears - 1 ) ), 0 )``````

Result:

Default:

Select 2018 in Slicer:

You can download the pbix file from this link: Calculate multi-year CAGR while ignoring slicer filter for date

Best Regards,

Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Highlighted
Microsoft

## Re: Calculate multi-year CAGR while ignoring slicer filter for date

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your problem or share me with your pbix file from your Onedrive for Business.

Best Regards,

Rico Zhou

Announcements

#### August Community Highlights

Check out a full recap of the month!

#### August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

#### Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors