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

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

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi,

      Could you please help me to check it? Somehow it still does not work for me.

      What I did:
      1. Create a new date table:

      Dates = Distinct(Data[Date])

      2. Create a new measure in the Data table

      Index 100 = 
VAR Date_index =
	Max (Dates[Date])
RETURN
	SUM(Data[Value])
	/ Calculate(max(Data[Value]);Data[Date]=Date_index)
	*100

      3. The slicer gets value from the Date Table.


      This is the result for me:

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

        Hi Anonymous,

         

        You need to set-up your Date column as a Date not as a hierarchy, when you do the hierarchy it "turns on" the Time Intelligence and fill outs the rest of the dates that are missing.

         

        Regards,

        MFelix