Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Calculate index base 100

Hello all, I would like to add indexing feature in my report. In short, user via filter can select which date they want to set as base (for example 1.1.2012). Then: - Value at 1.1.2012 = 100 - V...
Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi Anonymous,

     

    You can do ti using this simple steps:

     

    1. Add a calendar table to your data but leave it as a standalone table (do not make any relationship with other tables)
    2. Add a slicer to your report based on the Calendar Table
    3. Create the following measure on your Data Table
      Index 100 =
VAR Date_index =
    MIN ( 'Calendar'[Date] )
RETURN
    SUM ( Data[Value] )
        / CALCULATE ( MAX ( Data[Value] ), Data[Date] = Date_index )
        * 100
    4. If you want to have dates previous to the selected date not being calculated make this changes to your measure:
      Index 100 =
VAR Date_index =
    MIN ( 'Calendar'[Date] )
RETURN
    SWITCH (
        TRUE (),
        MIN ( Data[Date] ) < Date_index, 0,
        SUM ( Data[Value] )
            / CALCULATE ( MAX ( Data[Value] ), Data[Date] = Date_index )
            * 100
    )

    Final result is below:

     

    Regards,

    MFelix

     

MFelix's avatar
MFelix
Icon for Super User rankSuper User
9 years ago

Hi Anonymous,

 

Sorry for the late response, I have been looking at your question and once again waht is happening is that the time intelligence is kicking in when you create your calendar table, since you only have one date per month in your master data when you try to add the information based on a hierarchy slicer it goes wrong.

 

Once again you need to create the calendar table based on the values of your data:

Calendar = DISTINCT(Data[Date])

Make a relationship between this table and the Data table and add the Year and Month, put it in your slicer and should work as you need, for comparision I added a measure to the image below with the value of the selected date so you can see it always gets the correct value.

 

 

Regards,

MFelix

 

banditx42's avatar
banditx42
Regular Visitor
8 years ago

I'm trying to use this solution and place the results by state on a map.  How can I have the map show the index value for each state as of the last date in the range selected?  For example, if I select a base date of 1/1/2012 and a data date range of 1/1/2012 - 1/1/2017, how can I map the index as of 1/1/2017 for each state?  Each state's index should calculate its own value on 1/1/2017 and divide by the value on 1/1/2012. 

 

Thanks!