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,0...
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

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?