1. Window functions in Power BI
Hi there! In this session, we will cover the basics of window functions in Power BI.
2. Context and importance
The reason that window functions are important comes back to a key component of time series data: span. Data that occurs over time will carry a lot of information, and the amount of data we look at will shape what kind of questions we can answer.
Looking at the visual, we can see that these time series datasets share a common set of dates. However, one dataset includes all data before that date, while the second dataset only includes the month before. This significant difference in the amount of data provided will change the conclusions we draw from that data.
3. Expanding window functions - how it works
One very common type of window function is an expanding window function. With these calculations, we pick a singular anchor date or time and want to capture everything before or after that point in time.
Expanding window functions can be explicitly called out using a specific date, or can be used relative to a value such as TODAY. A common implementation of Expanding window functions is using the period to date functions, such as TOTALYTD and TOTALMTD.
4. Expanding window functions - use cases
Because of the amount of information, we will be capturing; this will tend to be a population-level analysis.
In the example code you see, we are calculating the total profit we have made since the beginning of 2020. Maybe we are interested in company performance since the pandemic. There is also an example of calculating the average of all the temperatures we have experienced in our dataset up until today. More commonly, we might want to calculate the total for all data in the current year, such as the total number of shipped products using a TOTALYTD function.
5. Rolling window functions - how it works
A second common type of window function is the rolling window function. Rolling window functions have relative anchor points, meaning they have a determined total span length but will refer to different dates as new data comes in.
The GIF below shows how our function will refer to new data as we get new data (ie, when the date changes).
6. Rolling window functions - use cases
Quite often, rolling window functions will be used to assess the current state of our observations while including recent history for context.
In industry, organizations use a variety of different rolling window functions. Common ones include a rolling 28-day average, or a rolling 12-month total. Analysts use these kinds of functions so they can see how a business or organization is behaving over a common timeframe. Maybe we want to see how an average month's sales over a four week period, or how the company stock has performed on a 12 month basis.
The example here shows a calculation that will provide the highest stock value we see over the last thirty days of data.
7. Let's practice!
So with that, let's look at how we can create these window functions and apply them to our Superstore dataset.