Forum Discussion
Time series indexation
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
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.
Community ChampionTry this:
Measure =
VAR minDate = [minDate]
Return
CALCULATE(SELECTEDVALUE(Table[Pct %]) ; ALL(Datatable) ; Datatable[Sales Month] = minDate)Br,
J
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]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
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 - tex628
Happy to hear that! I hope it continues just as well 🙂
/ J