I was delivering some training last week for a customer in Sydney and the participants had an interesting problem where they needed to be able to report on a rolling time window. Think of it a bit like Rolling 12 Months Sales, or average weekly sales over a rolling 4 week period, but instead this problem uses a rolling time window of just a few hours. I have decided to demonstrate how to solve this problem using my own solar electricity data and build a rolling average 3 hourly kWh consumption.
This is an interesting problem for a few reasons which I will explain below.
Split Your Date and Time
The first thing to note is that when ever possible, you should split your DateTime columns into separate Date and Time columns. The reason this is best practice is due to the way Power BI (The Vertipaq Engine) compresses data. The more unique values in any given column, the worse the data will be compressed. It therefore follows that 2 columns, one for date and one for time will compress better than 1 column containing DateTime. As a side point, you should also remove un-necessary precision from your time columns where possible. If you don’t need seconds, then truncate the seconds and just load minutes. If you don’t need minutes, then just load the hour, and so on. In summary, don’t load precision you don’t need in your reporting – your report performance will improve as a result.
Rolling 3 Hour Window
One consequence of splitting a DateTime column into 2 columns is that it is a bit harder to write a rolling 3 hour calculation. I can’t simply take the current DateTime and then go back in time and grab the data points that make up preceding 3 hours because the filters for date and time are applied independently. This can be clearly seen below from my work in progress measure shown below.
Note above, when the day changes, my rolling 3 hourly window measure (WIP) resets and is not correctly loading the data from late in the previous day. While this is a tricky problem, it is actually quite easy to solve as long as you break the problem into pieces and solve each part of the problem one at a time.
Watch Me Solve This Problem Myself
One thing I like to teach people is the process of how to solve a problem. Each problem is different and hence each solution is different. When I start the process I normally don’t know exactly what the final result will look like. All I do is work through the problem, one step at a time, until I have a working solution. If you watch my video below, you will see how I solved this particular problem, but more importantly you will see the approach I used and hopefully you will be able to adopt this approach yourself to solve your own problems.
My Final Measure
For the record, here is the final measure I ended up writing. Remember the key point is “How” I wrote the measure, not the measure itself.
Rolling 3 hour average = VAR ToTimeID = SELECTEDVALUE('Time'[TimeID]) VAR FromTimeID = ToTimeID - 11 VAR TodaysValue = CALCULATE( [Total kWh], FILTER(ALL('Time'),'Time'[TimeID] >= FromTimeID && 'Time'[TimeID] <=ToTimeID) ) VAR TodaysDate = SELECTEDVALUE('Calendar'[Date]) VAR YesterdaysDate = TodaysDate - 1 VAR ToTimeIDYesterday = 96 VAR PeriodsNeededFromYesterday = IF(ToTimeID < 12, 12-ToTimeID) VAR FromTimeIDYesterday = 96 - PeriodsNeededFromYesterday + 1 VAR YesterdaysValue = CALCULATE( [Total kWh], FILTER( ALL('Time'), 'Time'[TimeID] >= FromTimeIDYesterday && 'Time'[TimeID] <= ToTimeIDYesterday ), ALL('Calendar'[Date]),'Calendar'[Date] = YesterdaysDate ) RETURN YesterdaysValue + TodaysValue
I have also loaded my sample workbook here if you would like to take a look.