WINDOWs of the World

It's really hard to get very far in data science without knowing SQL. Within SQL there are different levels of knowledge that serve as progress markers, especially new to those learning the language (and yes, it is a programming language, according to the TIOBE Index). Those learning SQL will typically first learn syntax like SELECT, FROM, and WHERE to choose the filtered rows and columns from a database data table. They'll then learn how to sort and group the selected data, and then how to join and append data tables.

Window functions are a more advanced SQL concept to explore after that. These functions enable us to change the context in which we're evaluating a result, whether that's in SQL or a language like DAX. While we can set up SQL queries to database data sources within Power Query, in order to really maximize the capabilities of Power BI, we need to tap into the DAX language that makes things happen both on the front end and behind the scenes.

Power BI Weekly

Within DAX, there are two functions that directly support window calculations. First, there's the WINDOW function, which I explored in a recent video of my Power BI Weekly serial course. The WINDOW function enables us to expand the context in which we're evaluating a DAX measure result.

If we want to change the context in which we're evaluating a measure without expanding the entire window of the calculation, we can use the DAX OFFSET function.

Visualizing Window Functions

To really understand how window functions work, I find it incredibly helpful to visualize them. Here's what both the WINDOW and OFFSET DAX functions look like as calculations on the same data table.

1. The OFFSET DAX function enables us to move the context of the calculation to another date. For example, if we're trying to calculate the total sales on a previous day, the OFFSET function lets us point the calculation to another date in the table. Notice in this example, that the dates skip over the weekend. This is common with commodities data because the markets close over the weekend.
2. Conversely, the WINDOW DAX function also allows us to change the context of the calculation, but it works in a slightly different way than the OFFSET function. Instead of pointing to another date, the WINDOW function enables us to expand the time frame over which we're calculating the result. In this example, that means skipping over the weekend dates without data points. These types of calculations are really helpful for calculating the rolling average or the rolling mean of data points. We can see in the line chart below how the blue line representing the actual data values fluctuates quite a bit. We can smooth a lot of this out by calculating the 15-day rolling average on these same data points, which we see represented with the orange line.

Coming Up

I'm working on my next quarter of the Power BI Weekly series as well as other upcoming future courses. For those living in Houston, there's a Power BI user group meetup at the Microsoft offices in the City Centre area on October 19th from 4 to 6 p.m. More details on this to come soon, including a special session that's part of the user group schedule!

-HW