Calculate index base 100

[Anonymous]

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
- Value at other dates = Actual value at that date/Actual value at 1.1.2012 * 100

Do you have any suggestion on how to create the measures for calculating this? Thanks a lot!

[MFelix · Accepted solution]

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

[Anonymous]

Hello! 

Thanks for a good answer! My dataset, however, consist of various goods, ranging from A-I. Do you have any idea on how i can filter the index based on this to show for example only A compared to C? 

Thanks! 

[MFelix]

Hi @Anonymous,

When you mean that you want to have the comparition with A with C do you mean that the base value will be the the category or do you want it to continue to be the data but add next level of calculation to your index calculation? 

[Anonymous]

Hi @MFelix, 

So I have used the formula you provided above to get indexed values for all of the goods A-I. But instead of seeing all of the goods and their respective indexes at once, I want to be able to filter out some of the goods so I for example only can see two indexed values and compare them. So I want to use the index formula, but add another level of calculations so I can filter on the different products. 

[MFelix]

Hi @Anonymous 

If you can provide any more context would be great, if it possible to share a sample file it would be even better.

[MFelix]

Hi @Anonymous ,

Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.

[Anonymous]

Hi @MFelix, sorry for the late reply! I have made a Google Drive folder with some sample data. You should find it through this link: 
https://drive.google.com/drive/folders/101lTXUDi-603BFeJxx_DzX2Hcl_a2jID?usp=sharing

Thanks for the help so far, appreciate it! 

[MFelix]

Hi @Anonymous ,

If I understood correctly what you need is to change the metric used based on a slicer in this case you can create a parameter field:

Then select the measures you want for your parameter:

Now you can use the slicer to choose:

On the Y axis instead of the measures use the parameter field.

[Anonymous]

That worked wonders, @MFelix. Appreciate your assistance, thanks! 

[Anonymous]

Hi,

Thanks for your suggestion. My dataset consists of many different items, and I need to have index value for each of the items. However, this methods sum up all value. Do you have any ideas how to solve that?

[MFelix]

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]

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]

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]

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]

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]

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]

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

[Anonymous]

Hi @MFelix,

I have still one more question? With this solution, is there still a way to use hierarchy slicer?

I tried to do so, and it results in infinity:

In the data table, I create two new columns, which get the year and month from the date, and put them in hierarchy level. The hierarchy slicer is getting data from that year hierarchy. The data is still ok if the date period chosen include the date which I set as base for index. But if the period does not include, the data shows infinity.

Thanks you!

[MFelix]

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]

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!