Time series indexation

[tsol01]

Dear all,

I am trying to calculate an index value for time series data which should be used as a basis for % change calculations.

Desired outcome:

Sales month		Pct %		index basis		calculated value
01.01.2019		43,1		43,1		43,1/43,1
01.02.2019		38,5		43,1		38,5/43,1
01.03.2019		35,7		43,1		35,7/43,1
...

The index basis should be calculated automatically based on the slicer selection (supplier, category levels 0-2, product number).

All data is stored in one table.

I created two measures until now:

minDate = CALCULATE(min(Data_Table[Sales date]), ALLSELECTED(Data_Table[Sales date]))

This measure should return the first date (in case of the example 01.01.2019, could however also be e.g. the 01.03.2019 or something else)

indexValue = CALCULATE(AVERAGE(Data_Table[Pct %]), filter(Data_Table, Data_Table[Supplier match]=Data_Table[Supplier match]), filter(Data_Table, Data_Table[Level0 match]=Data_Table[Level0 match]), filter(Data_Table, Data_Table[Level1 match]=Data_Table[Level1 match]), filter(Data_Table, Data_Table[Level2 match]=Data_Table[Level2 match]), filter(Data_Table, Data_Table[Product number ]=Data_Table[Product number ]), filter(Data_Table, Data_Table[Sales date]=[minDate]))

Since the table can contain multiple rows per month I wanted to calculate the average for the first month and use this value as basis for further calculations. 

However, at the moment the values don't look like in the desired outcome above but as follows (while the min date is correct): 

Sales month		Pct %		index basis		minDate
01.01.2019		43,1		43,1		01.01.2019
01.02.2019		38,5		38,5		01.01.2019
01.03.2019		35,7		35,7		01.01.2019
...

If I use a specific date (e.g. 01.01.2019) instead of minDate as filter the result looks like this:

Sales month		Pct %		index basis		minDate
01.01.2019		43,1		43,1		01.01.2019
01.02.2019		38,5				01.01.2019
01.03.2019		35,7				01.01.2019
...

Then, the calculation (pct % / index basis) would result in infinity for 02 and 03/2019.

Do you have an idea how to resolve the problem with measures or in any other way (indexate month % to first value)?

Thank you very much in advance!

[vanessafvg]

are you able to provide the data or a pbix?

[tsol01]

Unfortunately I can't upload files here but I uploaded a sample data set to Wetransfer - hope this works!

Sample data set 

[vanessafvg]

hi got the file the problem is your date, your date looks like a lookup table value.

 In order to identify the problem i need  to replicate your pbix.  If you can share the pbix that would be easier, if not please provide the dates.

[tsol01]

It seems like the upload on wetransfer changed the column formatting. The dates in the column are correct, just the excel format needs to be changed:

Unfortunately I can't share the PBIX file as it contains sensitive information, I'm sorry!

[vanessafvg]

hi, so if i understand correctly what you trying to do is fill down the value when there isn't a valid value, i can't actually see a way to do it currently but ill keep digging and if anyone else wants to jump in thats great too

[tsol01]

Thank you very much! In fact I just want to always use the average for the first date as basis for the calculation. Unfortunately I could not find a way on how to filter the measure dynamically to the first date for the selected category/supplier/... for all rows 😞

The goal is to have a line chart where the line always starts at 100% and then the increases/decreases are displayed based on the first reference value. 

[tex628]

Try this:

Measure = 
VAR minDate = [minDate]
Return
CALCULATE(SELECTEDVALUE(Table[Pct %]) ; ALL(Datatable) ; Datatable[Sales Month] = minDate)

Br,
J

[tsol01]

Thank you very much for your input! With this method the selection of the min date works fine. However, selectedvalue is returning blank (I assume because there can be multiple values per month which need to be averaged first). But I don't have an idea how to combine average with selectedvalue. Do you have an idea on that? 

[tex628 · Accepted solution]

As long as the filter on mindate works i dont believe you need to combine the two, if theres more than 1 value per date in [Ptr %] i believe that all we need to do is change it to AVERAGE().

Measure = 
VAR minDate = [minDate]
Return
CALCULATE(AVERAGE(Table[Pct %]) ; ALL(Datatable) ; Datatable[Sales Month] = minDate)

Br,
J

[tsol01]

I came to the same insight just a few minutes ago 🙂 thank you very much for your support! Saved my day!

I just changed it a bit and removed the ALL clause so the solution looks like this now:

Get the first date:

minDate = CALCULATE(min(Table[Sales date]), ALLSELECTED(Table[Sales date]))

Calculate the index basis:

indexValue = var minDate = [minDate] Return CALCULATE(AVERAGE(Table[Pct %]), Table[Sales date]=minDate)

Indexate with the index basis:

Pct indexed % = AVERAGE(Table[Pct %])/[indexValue]

[tex628]

Happy to hear that! I hope it continues just as well 🙂

/ J