Dynamic Consumer Price Index (CPI) by Date Slicer

[Anonymous]

I'm trying to create a dynamic calculation of Consumer Price Index (CPI) that sets the base year depending on my Date Slicer.

Index is calculated by the following principle:

(Year 2 of $250,000 / Base year of $150,000) * 100 = 167

My input table looks like this:

Year	Consumer Price Index 1980
1980	100
1981	110
1982	120
1983	130
1984	140
1985	130
1986	140
1987	150
1988	160
1989	170
1990	180
1991	190
1992	200
1993	230
1994	240
1995	250
1996	260
1997	270
1998	280

 

I have succeeded with creating a measure that statically sets the Base Year to 1990, however this of course does not respond to my Date Slicer.

Consumer Price Index 1990 = 
DIVIDE(
    CALCULATE(SUM(tblCPI[Consumer Price Index 1980]));
    CALCULATE(SUM(tblCPI[Consumer Price Index 1980]);FILTER(ALL(tblCPI);tblCPI[Year] = 1990))
) * 100

However i fail when i try to set the Base Year dynamically with MIN instead of 1990.

Consumer Price Index Dynamic = 
DIVIDE(
    CALCULATE(SUM(tblCPI[Consumer Price Index 1980]));
    CALCULATE(SUM(tblCPI[Consumer Price Index 1980]);FILTER(ALL(tblCPI);tblCPI[Year] = MIN(tblCPI[Year]))
) * 100)

Any help would be much appreciated!

[v-piga-msft · Accepted solution]

Hi @Anonymous,

Please try the formula below.

Consumer Price Index Dynamic =
VAR yearbase =
    CALCULATE ( MIN ( 'tblCPI'[Year] ), ALLSELECTED ( tblCPI ) )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( tblCPI[Consumer Price Index 1980] ) ),
        CALCULATE (
            SUM ( tblCPI[Consumer Price Index 1980] ),
            FILTER ( ALL ( tblCPI ), tblCPI[Year] = yearbase )
        )
    )
        * 100

Best Regards,

Cherry

[Vvelarde]

@Anonymous

 Hi, lets try with this:

Consumer Price Index Dynamic =
VAR YearBase =
    SELECTEDVALUE ( tblCPI[Year] )
RETURN
    DIVIDE (
        CALCULATE ( SUM ( tblCPI[Consumer Price Index 1980] ) );
        CALCULATE (
            SUM ( tblCPI[Consumer Price Index 1980] );
            FILTER ( ALL ( tblCPI ); tblCPI[Year] = YearBase )
        )
            * 100
    )

 Regards

[siddhartha0208]

Hello There, this is a great solution. I've a bit complex problem with added country field and I need to calculate this for each country - any guidance?

e.g. my data looks like following:

Which can be represented in pivotted format like:

Now user selects year 2016 and I need to calculate CPI based on the values of 2016 to output as following:

Any guidance?