Hi,
This might be a FAQ but I have done some research and can't find anything that answers the question I have.
I am trying to write a measure that calculates an energy consumption baseline based on 20 business days from the selected date. I want the measure to be dynamic in a way that it always calculates this baseline once a date has been picked and the data is available in the dataset.
The main issue I a currently having is getting my measure to filter based on the selected date and return a table with the last 20 business days, currently the measure I have written returns a blank table and I have tried different iterations but get the same result.
My dataset contains multiple same date entries with differnt timestamps and for different buildings.
Another question, while using the slicer visual as my date picker, is it possible to specify the start date as the date which the measure works with to calculate the baseline? I tried a simpler measure using a card visual and the slicer visual and noticed that the SelectedValue function only works when the slicer visual is set to either dropdown or list. Is there a way to use the Between style on the slicer and have the measure only take the start date?
I have attached screenshots of the measures I have written so far.
Any help or hints I can get is appreciated.
@parry2k @amitchandak @Idrissshatila @Ritaf1983
Any additional informaton needed, I will provide.
Thank you!
This is how I would write it. Please check if it matches your expectations.
Baseline =
var h = SELECTEDVALUE(Sheet1[Time])
var d = SELECTEDVALUE(Sheet1[Date])
var a = CALCULATETABLE(Sheet1,allselected(),Sheet1[Date]<=d,Sheet1[Time]=h,Sheet1[WeekdayType]="Weekday")
var b = TOPN(20,a,[Date],DESC)
var c = TOPN(15,b,[HourlyAverage],DESC)
return averagex(c,[HourlyAverage])What should happen if fewer than 20 data points are available?
This is how I would write it. Please check if it matches your expectations.
Baseline =
var h = SELECTEDVALUE(Sheet1[Time])
var d = SELECTEDVALUE(Sheet1[Date])
var a = CALCULATETABLE(Sheet1,allselected(),Sheet1[Date]<=d,Sheet1[Time]=h,Sheet1[WeekdayType]="Weekday")
var b = TOPN(20,a,[Date],DESC)
var c = TOPN(15,b,[HourlyAverage],DESC)
return averagex(c,[HourlyAverage])What should happen if fewer than 20 data points are available?
Note that the baseline measure is calculated for every single time slot separately.
That's one of the reasons why your sum is so high. If you want the baseline to be pegged at 3pm each day then you need to modify the measure.
What is this WeekDay Type "Event" ?
The TimeslotTable doesn't help, it only introduces additional cartesians.
Helpful resources
New forum boards available in Real-Time Intelligence.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
