Forum Discussion
Calculate index base 100
Hi Anonymous,
You can do ti using this simple steps:
- Add a calendar table to your data but leave it as a standalone table (do not make any relationship with other tables)
- Add a slicer to your report based on the Calendar Table
- 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 - 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
Super UserHi 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
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
Hi MFelix,
That works! Many thanks for your help!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
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!
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!
