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,

 

I just made a simple table that not very fancy calculation what type of calculation you want in the Total row?

 

Regards,

MFelix

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hello,

Please ignore my previous question, that problem is solved. However, I encounter another one. Only the value at the date that I choose to index return correctly (100). Other returns infinity. 

 

 

I use the same DAX as you suggested.


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

    In my calculations I assumed you add a date, how are you setting up your filtering date?

     

    I can see in your print that you only have year and month.

     

    Regards

    MFelix

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi,

      Following your advice, I set up another Date table, which is not linked to my data table. 

       

      The hierarchy filter is taking date from that separate Date table.

       

      I have only one value for each of the month (on the first day), so for the date hierarchy I just keep month and year, removing the date since it is not needed.

      I also tested again with a simple dataset and the problem still remains.

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

        Hi Anonymous,

         

        The problem is that you Dates in calendar are sequencial and the ones in the Data are not.

         

        Please create your Calendar Table based on the Data, add a new table with the following formula:

         

        DATES = DISTINCT(Data[Date])

         

        And then change your variable in the measure to this:

        VAR Date_index =
    MAX ( DATES[Date] )

        Below see the result of this new setup and the old setup:

         

        Regards,

        MFelix