Calculate multi-year CAGR while ignoring slicer filter for date

[Anonymous]

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 

[v-rzhou-msft]

Hi @Anonymous 

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

[v-rzhou-msft]

Hi @Anonymous 

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 won’t 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. 

[Anonymous]

Hi @v-rzhou-msft - 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

[v-rzhou-msft · Accepted solution]

Hi @Anonymous 

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.

[Ashish_Mathur]

Hi,

Does this work?

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