How to write a measure that filters data based on a date selection on a visual

[JohnnyCenaa]

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!

[lbendlin]

trying to write a measure that calculates an energy consumption baseline based on 20 business days from the selected date.

20 ? Later down you mention 30?  

What is your definition of a business day?  Do you have an appropriate flag in your calendar table?  Are all your users in the same time zone and/or culture?

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[JohnnyCenaa]

Thanks @lbendlin for the tips. I have edited the mistake I made in the initial post, I meant to type 20 business days. What I mean by business days is working days in the week, this excludes weekends and holidays. I have a holiday table that I used to filter out holidays from my data or reference in my measures and M code to filter out when calculating.

I have attached a sample of the data I am working with, the blank Column is ideally were i want my calculated result to be. I have been Playing around with M code instead to have my data already calculated the baseline and then easily display it in a visual rather than my initial attempt of using a visual and have my measure calculated based on the date picked.

I am currently trying to write an M code that calculates the baseline for each hour of the day. The baseline would be the average of the largest 15 electricity use from the last 20 business days and this would be calculated for each hour of the day. For example, the calculated would take the largest 15 electricity use values for 3 pm from the last 20 business days, average these values and get the baseline for 3 pm of the day it is calculating for, this will be done for every hour of the day. The resulting baseline calculated will then be in the column beside the hours.

This is my M code

let
Source = Query5, // Replace YourTable with the actual name of your table
Filtered = Table.SelectRows(
Source,
each [WeekdayType] = "Weekday" // Filter for weekdays
),
Sorted = Table.Sort(Filtered, {{Date, Order.Ascending}, {Time, Order.Ascending}}),
AddWeekdayNumber = Table.AddColumn(Sorted, "WeekdayNumber", each Date.DayOfWeek([Date], Day.Monday)),
AddDateRank = Table.AddColumn(AddWeekdayNumber, "DateRank", each [WeekdayNumber] + (Number.From([Date]) - Number.From(List.Min(Sorted[Date])))),
FilteredLast20Weekdays = Table.SelectRows(AddDateRank, each [DateRank] >= 0 and [DateRank] < 20),
AddHourOfDay = Table.AddColumn(FilteredLast20Weekdays, "HourOfDay", each time.Hour([Time])),
SortedByHour = Table.Sort(AddHourOfDay, {{"HourOfDay", Order.Ascending}, {"Date", Order.Ascending}}),
Grouped = Table.Group(
SortedByHour,
{"HourOfDay"},
{{"Top15", each Table.FirstN(_, 15), type table}}
),
ExpandTop15 = Table.ExpandTableColumn(Grouped, "Top15", {"HourlyAverage"}, {"HourlyAverage"}),
CalculateAverage = Table.Group(
ExpandTop15,
{"HourOfDay"},
{{"AverageUsage", each List.Average([HourlyAverage]), type number}}
)
in
CalculateAverage

The sample data 

ID	Electricity use	Date	Time	Weekday	Baseline value
13013	396	2023-01-03	12:00:00 AM	Weekday
10472	1352.8	2023-01-03	12:00:00 AM	Weekday
10832	4471.4	2023-01-03	12:00:00 AM	Weekday
9958	245.5	2023-01-03	12:00:00 AM	Weekday
12330	296.1	2023-01-03	12:00:00 AM	Weekday
12408	352.5	2023-01-03	12:00:00 AM	Weekday
12260	18384.7	2023-01-03	12:00:00 AM	Weekday
12981	469.4	2023-01-03	12:00:00 AM	Weekday
10071	264.4	2023-01-03	12:00:00 AM	Weekday
13001	6302.8	2023-01-03	12:00:00 AM	Weekday
9506	126.6	2023-01-03	12:00:00 AM	Weekday
13459	101.9	2023-01-03	12:00:00 AM	Weekday
13431	22.7	2023-01-03	12:00:00 AM	Weekday
12334	907.9	2023-01-03	12:00:00 AM	Weekday
10396	5407.6	2023-01-03	12:00:00 AM	Weekday
9922	239	2023-01-03	12:00:00 AM	Weekday
13095	240.1	2023-01-03	12:00:00 AM	Weekday
13006	1003.1	2023-01-03	12:00:00 AM	Weekday
9436	2832.3	2023-01-03	12:00:00 AM	Weekday
13536	709.3	2023-01-03	12:00:00 AM	Weekday
13165	298.5	2023-01-03	12:00:00 AM	Weekday
13009	5894.8	2023-01-03	12:00:00 AM	Weekday
13025	918	2023-01-03	12:00:00 AM	Weekday
13748	2431.7	2023-01-03	12:00:00 AM	Weekday
10002	204.8	2023-01-03	12:00:00 AM	Weekday
13100	262.8	2023-01-03	12:00:00 AM	Weekday
12424	234	2023-01-03	12:00:00 AM	Weekday
13756	584.1	2023-01-03	12:00:00 AM	Weekday
12498	1425.1	2023-01-03	12:00:00 AM	Weekday
12472	165.6	2023-01-03	12:00:00 AM	Weekday
12426	645	2023-01-03	12:00:00 AM	Weekday
12966	2845.6	2023-01-03	12:00:00 AM	Weekday
9874	310.5	2023-01-03	12:00:00 AM	Weekday
9986	264.5	2023-01-03	12:00:00 AM	Weekday
9946	200.9	2023-01-03	12:00:00 AM	Weekday
12408	352.5	2023-01-03	12:00:00 AM	Weekday
11912	272.9	2023-01-03	12:00:00 AM	Weekday
13943	6572.5	2023-01-03	12:00:00 AM	Weekday
13099	249.4	2023-01-03	12:00:00 AM	Weekday
13691	1411.5	2023-01-03	12:00:00 AM	Weekday
9512	534	2023-01-03	12:00:00 AM	Weekday
10826	171.8	2023-01-03	12:00:00 AM	Weekday
13119	14884.2	2023-01-03	12:00:00 AM	Weekday
9926	190.6	2023-01-03	12:00:00 AM	Weekday
9954	256.5	2023-01-03	12:00:00 AM	Weekday
13639	165.3	2023-01-03	12:00:00 AM	Weekday
9854	332.1	2023-01-03	12:00:00 AM	Weekday
12152	32.2	2023-01-03	12:00:00 AM	Weekday
14011	1910.4	2023-01-03	12:00:00 AM	Weekday

[lbendlin]

your sample data only covers a single day. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

[JohnnyCenaa]

This is a link to a sample dataset of one month. 

https://docs.google.com/spreadsheets/d/1BM0YAxI59mmb4eiaJJib29UDzfvPH7Lp/edit?usp=sharing&ouid=11579... 

[lbendlin]

Thank you.  Please also include your calendar table with the Business Day column.  Your data has a 15 minute sampling rate - do you want to also filter by business hours? Or do you want to consider the complete 24 hours for each business day?  In what time zone?

[JohnnyCenaa]

Apologies for the oversight, I have uploaded a new sample data https://docs.google.com/spreadsheets/d/1yaEXBdLdkhaRLaVWRv0Kfeac3-U_xnNj/edit?usp=drive_link&ouid=11... 

This dataset contains 3 months worth of data, and a column to describ weekday, weekend, or holiday. I have also aggregated the data into 1 hour intervals.

So what i am trying to do is have the calculation be done for each hour of the day (if possible). I want it to look at the last 20 days from a date and have it take the largest 15 electricity use and average the values to get the baseline for each hour. For example, for 3 pm in a day it would look at all the 3 pm electricity use data in the past 20 business days, take the largest 15 values and average them to get the baseline for 3 pm of the start day. 

Is it possible to have this calculation done for all available dates and hours in the dataset, as long as data for the previous 20 business days are available.

Thank you.

[lbendlin]

Can you please check the link  - it asks for access.

[JohnnyCenaa]

Try this link

https://docs.google.com/spreadsheets/d/1yaEXBdLdkhaRLaVWRv0Kfeac3-U_xnNj/edit?usp=sharing&ouid=11579...

Thanks

[lbendlin · Accepted solution]

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?

[JohnnyCenaa]

Thank you.

This is very close to what I am trying to do.

It worked for some of the datasets. I then went further down (since I have a larger dataset) and noticed that the Baseline being returned is slightly off (I have a separate database with the baseline values for each hour and day). 

Do you have any idea why it seems to be slightly off when I move further down the dates?

If there are fewer than 20 data points I would like it to return an error saying something like "Baseline could not be calculatted due to missing data points"

Thanks a lot for the help with this!!

Any ideas as to why the slight discrepancies? 

The difference between what your measure calculates and the actual baseline I have is only about 10 to 15.

Asides all that, your measure delivers the expected result. Thank you.

If you have any ideas or suggestions on how it can be improved, please share.

Thank you once again!

[lbendlin]

- my baseline includes the current day. Maybe yours doesn't.  You can adjust the code accordingly.

- you cannot return numbers or text in the same measure. Your only option is to suppress the result by returning BLANK() if there are fewer than 20 data points. Again, you can adjust the measure accordingly.

[JohnnyCenaa]

Hi,

Thanks for the help earlier.

Currently I am adjusting the code you provide to suit my requirements. 
I want it now return the sum of HourlyAverage between 1pm and 3pm after the average has been calculated. But it returns a sum way more than the time range given

Please could you tell me what i am doing wrong. Thank you.

This is the code below

IDABaseline =

VAR h = SELECTEDVALUE(Query5[Time])

VAR d = SELECTEDVALUE(Query5[Date])

VAR a =

    CALCULATETABLE(

        Query5,

        ALLSELECTED(),

        Query5[Date] < d,

        Query5[Time] = h,

        Query5[WeekdayType] = "Weekday"

    )

VAR b = TOPN(20, a, [Date], DESC)

VAR c = TOPN(15, b, [HourlyAverage], DESC)

VAR avgHourly =

    AVERAGEX(

        c,

        [HourlyAverage]

    )

VAR filteredHours =

    FILTER(

        c,

        Query5[Time] >= TIMEVALUE("1:00 PM") && Query5[Time] <= TIMEVALUE("3:00 PM")

    )

RETURN

    IF(

        COUNTROWS(b) = 20,

        SUMX(filteredHours, [HourlyAverage]),

        BLANK()

    )

[lbendlin]

The baseline is calculated based on the exact time slot (regardless which one - whatever your filter context is) . You cannot then arbitrarily expand that to cover other timeslots.  You'll have to base that computation on the raw data, not the baseline.

[JohnnyCenaa]

Okay, but I am trying to add the values in the time slot for the baseline calculated. The baseline is the data I want to work with to get the sum for the 3 hours mentioned.

[lbendlin]

As I said - your timeslot range will have to be a separate measure.  You can then compare that to the baseline measure. Or use the graphical solution where you show the actuals and the baseline side by side.

[JohnnyCenaa]

Sorry about that, I didn't fully understand what you said earlier. 

I followed what you said and created a calculated table that filters my main data to the time range I want . 
I then wrote this measure below but it gives totally wrong values

SUMX(

    TimeSlotTable,

    CALCULATE(

        [Baseline], TimeSlotTable[Time]

    )

)

Please what am I doing wrong

[lbendlin]

would you mind posting a sample pbix?  Or use mine and add your formulas?

[JohnnyCenaa]

My version of Power BI is older than yours, so I couldn't use the file you sent.
I have attached a sample data file.

If you run into any issues with the file let me know. 

All the measure I have written so far are on it. On Page 1 the visual shows the measures I have been playing around with. IDABaseline is the name of the main measure I am working on, the IDABaselineTest and SumBetween 1PMAnd3PM measures are test measures I have been trying out.

Thanks for the help.

https://drive.google.com/file/d/1q6Cha_ddpD8x5sUL6e93SyxCkcHJGoYo/view?usp=sharing  

[lbendlin]

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.

[JohnnyCenaa]

Hi,

Sorry to disturb you, please do you have an idea how I can write this measure?

Was the explanation I gave previouly clearer? If you need anymore info please let me know and I will provide it.