cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
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
Microsoft

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

Hi @DataNewbie 

I build a new table to have a test.

Industry_Revenue Table:

1.png

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:

2.png

Select 2018 in Slicer:

3.png

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.

View solution in original post

5 REPLIES 5
Highlighted
Super User V
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
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
Microsoft
Microsoft

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

Hi @DataNewbie 

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.

1.png

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:

2.png

Select 2018:

3.png

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
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
Microsoft

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

Hi @DataNewbie 

I build a new table to have a test.

Industry_Revenue Table:

1.png

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:

2.png

Select 2018 in Slicer:

3.png

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.

View solution in original post

Highlighted
Microsoft
Microsoft

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

Hi @DataNewbie 

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

Helpful resources

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 Kudoed Authors