Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Dynamic Consumer Price Index (CPI) by Date Slicer

 

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!

 

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    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

3 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      siddhartha0208
      Icon for Helper I rankHelper I
      6 years ago

      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?

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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