Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

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


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?


View solution in original post

Super User
Super User

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
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to

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

Source = Query5, // Replace YourTable with the actual name of your table
Filtered = Table.SelectRows(
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(
{{"Top15", each Table.FirstN(_, 15), type table}}
ExpandTop15 = Table.ExpandTableColumn(Grouped, "Top15", {"HourlyAverage"}, {"HourlyAverage"}),
CalculateAverage = Table.Group(
{{"AverageUsage", each List.Average([HourlyAverage]), type number}}


The sample data 

IDElectricity useDateTimeWeekdayBaseline value
130133962023-01-0312:00:00 AMWeekday 
104721352.82023-01-0312:00:00 AMWeekday 
108324471.42023-01-0312:00:00 AMWeekday 
9958245.52023-01-0312:00:00 AMWeekday 
12330296.12023-01-0312:00:00 AMWeekday 
12408352.52023-01-0312:00:00 AMWeekday 
1226018384.72023-01-0312:00:00 AMWeekday 
12981469.42023-01-0312:00:00 AMWeekday 
10071264.42023-01-0312:00:00 AMWeekday 
130016302.82023-01-0312:00:00 AMWeekday 
9506126.62023-01-0312:00:00 AMWeekday 
13459101.92023-01-0312:00:00 AMWeekday 
1343122.72023-01-0312:00:00 AMWeekday 
12334907.92023-01-0312:00:00 AMWeekday 
103965407.62023-01-0312:00:00 AMWeekday 
99222392023-01-0312:00:00 AMWeekday 
13095240.12023-01-0312:00:00 AMWeekday 
130061003.12023-01-0312:00:00 AMWeekday 
94362832.32023-01-0312:00:00 AMWeekday 
13536709.32023-01-0312:00:00 AMWeekday 
13165298.52023-01-0312:00:00 AMWeekday 
130095894.82023-01-0312:00:00 AMWeekday 
130259182023-01-0312:00:00 AMWeekday 
137482431.72023-01-0312:00:00 AMWeekday 
10002204.82023-01-0312:00:00 AMWeekday 
13100262.82023-01-0312:00:00 AMWeekday 
124242342023-01-0312:00:00 AMWeekday 
13756584.12023-01-0312:00:00 AMWeekday 
124981425.12023-01-0312:00:00 AMWeekday 
12472165.62023-01-0312:00:00 AMWeekday 
124266452023-01-0312:00:00 AMWeekday 
129662845.62023-01-0312:00:00 AMWeekday 
9874310.52023-01-0312:00:00 AMWeekday 
9986264.52023-01-0312:00:00 AMWeekday 
9946200.92023-01-0312:00:00 AMWeekday 
12408352.52023-01-0312:00:00 AMWeekday 
11912272.92023-01-0312:00:00 AMWeekday 
139436572.52023-01-0312:00:00 AMWeekday 
13099249.42023-01-0312:00:00 AMWeekday 
136911411.52023-01-0312:00:00 AMWeekday 
95125342023-01-0312:00:00 AMWeekday 
10826171.82023-01-0312:00:00 AMWeekday 
1311914884.22023-01-0312:00:00 AMWeekday 
9926190.62023-01-0312:00:00 AMWeekday 
9954256.52023-01-0312:00:00 AMWeekday 
13639165.32023-01-0312:00:00 AMWeekday 
9854332.12023-01-0312:00:00 AMWeekday 
1215232.22023-01-0312:00:00 AMWeekday 
140111910.42023-01-0312:00:00 AMWeekday 

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.

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?

Apologies for the oversight, I have uploaded a new sample data 


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.

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

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?


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!

- 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.



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 a =
        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 =
VAR filteredHours =
        Query5[Time] >= TIMEVALUE("1:00 PM") && Query5[Time] <= TIMEVALUE("3:00 PM")
        COUNTROWS(b) = 20,
        SUMX(filteredHours, [HourlyAverage]),

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.

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.

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.

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

        [Baseline], TimeSlotTable[Time]

Please what am I doing wrong

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

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.  

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.


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. 

Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors