PowerBI Real-Time Tumbling Windows

Every month Microsoft rolls out new features for Power BI, and I have been impressed by the pace and complexity of this cadence. One area that Microsoft continues to improve at a sharp pace is time intelligence (year-to-date, month-to-date, etc.), which is generally one of the most important features of any dashboarding solution.

I have followed these updates closely as I’ve started exploring real-time dashboarding. In particular, dashboards that can provide up-to-the-minute data to provide business leaders with the ability to course correct on an intraday basis. My preferred method for doing this is to leverage the DirectQuery capability that Microsoft offers in Power BI, which creates a live database connection to query data in real time. In a recent update, Microsoft has also deployed the ability to have hybrid dashboards that can have DirectQuery connections to multiple databases as well as the ability to schedule much bigger data imports from multiple databases, a significant shift for companies with datasets that span an IT environment.

Often, we like to see how the business is performing against a day from the prior week, though not just the total number of actions of that day, rather how we are tracking against a similar point in time on that day. In this blog post, I will demonstrate how to create a tumbling window, in essence, a moving range of time, by using DAX columns and measures that can support that type of solution (DAX is the functional language of Power BI).

The Solution

As a sample scenario, let’s imagine we are looking at a PowerBI dashboard for sales (with a DirectQuery connection to a single production database) on a Monday at midday and we’d like to compare that to sales counts on the Monday of the prior week, but only up to midday as opposed to all sales for that day.

A simple DAX measure to count sales could be:

Sales Count = CALCULATE( COUNT( Order[ID] ) , Order[Status] = “Purchased” )

When combined with a relative date slicer set for today, this measure can give you up-to-the-minute counts, depending on the data refresh increments set in the PowerBI service, or each time you hit the Refresh button in the PowerBI service.

As we start to think about how we can obtain our prior week counts, we need an appropriate time intelligence element. While there are several methods to calculate prior week counts, in this case I chose to create a column on my fact table (the core table of the model) called Days In Past (see my prior blog post for additional details on rolling date windows). The DAX that I used for this additional column is:

Days In Past = DATEDIFF( Order[Date] , NOW() , DAY )

Now that we have our Days In Past factor, the lower bound of our tumbling window as a filter in our CALCULATE DAX function is set. However, a little additional thought is needed on our upper bound as this is what will move (i.e., “tumble”) throughout the prior day to match the current point on our current day. I have used minutes as my time measure in this case and created an additional column on my fact table:

Minutes In Past = DATEDIFF( Order[Date] , NOW() , MINUTE )

At this point, all we have left is to create our Last Week calculation and incorporate the Sales Count measure from earlier:

Sales Count Last Week =

CALCULATE( [Sales Count] , Order[Days In Past] = 7 , Order[Minutes In Past] >= 10440 )

You may notice here that 10,440 minutes is slightly more than seven days’ worth of minutes. This is because the Power BI servers run on Coordinated Universal Time (UTC) time, so while 7 days x 24 hours x 60 minutes = 10,080 minutes, my development takes place on a machine on U.S. Central time and there is a six-hour offset to UTC, getting me to 10,440 minutes.

An implementation of this in a PowerBI dashboard is shown below. In the left column we have not included our upper bound tumbling window minute filter, and we are comparing live 10 a.m. Thursday, September 6 sales against our total end-of-day sales for Thursday, August 30. In the right example, we have included our upper bound tumbling window minute filter and we can see that we are actually performing ahead of expectations at 10 a.m. compared to our 10 a.m. total on the same day last week. While it is valuable to see our end-of-day targets, it becomes exponentially more valuable to see how we are tracking in real time to have actionable intraday business insight to divert resources as needed.

Considerations

At this point, we have our columns and measures created, but there are a few other considerations and options to maximize the display of our data.

Above, we have shown how to compare real-time, intraday sales against those of the prior week; however, we can also think about this for the prior month, or even average our values for prior weekdays for the last four, eight, or more weeks.

Additionally, your data may not be limited to a single time zone. In that case, you’d need to consider how to normalize your data or adjust your measures to account for the time discrepancy. Merging timezone data based on sales locations might be one approach.

Final Thoughts

DirectQuery and Power BI provide a great way to load and visualize real-time streaming data when coupled with a smartly architected dataset, and this implementation demonstrates a powerful use case of Power BI for a business user. If we think about the current evolution of technology, particularly streaming data from sensors and devices under the Internet of Things umbrella, being able to compare against prior week’s data on a real-time basis provides for nearly instant gap analyses on device performance as well as a multitude of other use cases.

Feel free to reach out to me at aforeman@credera.com or contact us online if you have any questions or for a conversation around how we can help you deliver a real-time data streaming solution.

• Analytics & Insights
• DirectQuery
• Power BI