Applications of window functions
1. Applications of window functions
Hi there! In this session, we will be covering some more advanced applications of window functions.2. Context and importance
Both expanding and rolling window functions provide a great deal of utility from an analytical perspective. These functions are more frameworks for how to apply your various calculations. As long as the data exists, you can use any DAX function you want, like some of the more advanced functions listed on the screen, and have the window function define which time span the calculation applies to.3. SAMEPERIODLASTYEAR vs. PARALLELPERIOD
To apply window functions in a variety of different ways, Power BI and DAX have several options to choose from. In particular, there are two key functions that pop up frequently: SAMEPERIODLASTYEAR, and PARALLELPERIOD. On the surface, these functions seem to be very similar, but there are some important differences. SAMEPERIODLASTYEAR takes a set of dates and will return a table of dates exactly one year prior. You can see in the DAX syntax that this function only requires a date field. PARALLELPERIOD takes a date field, and returns a table of dates that have been shifted a specified number of intervals. The key difference to this function is that the shifted dates can be any size interval, such as a DAY or MONTH, as well as a YEAR. Both of these functions provide a mechanism to compare a calculation against a similar period, and are often used as a filter in a CALCULATE statement.4. Analyzing the same window as last year
Let's say that we defined a window function and wanted to know what this would have looked like in the past. In this case, we can apply our window function to the related period from a previous year. This allows us to get the context to compare our current result to the same relative point in time from the past. This kind of analysis typically follows three steps: First, calculate an important measure or KPI. Second, apply that calculation to another period of time, such as last year or six months ago. Third, find the difference and relative change in these values to understand our performance.5. Analyzing the same window as last year examples
These kinds of calculations are very common in the industry, since they provide two very important data points: How are we doing today? How are we doing to the same calculation from last year? Having this context is key, since we can't know if we are improving or declining without that historical context. In this example, we have a measure that calculates the average cost at our stores for the last thirty days. Using the SAMEPERIODLASTYEAR function, we can compare the average cost of this year to last years over the same set of dates.6. Calculating year over year change
Techniques like the one you just saw lead the way in using that data to track long-term progress over different data points. We can use historical data to understand, in an "apples to apples" way, how our data has changed over time. This is known as a Year over Year calculation. The idea is to take a specific time span, find the difference between data from this year and last year, and report the findings. Typically, this is shown as a percentage. In this example, we have a measure that calculates the total revenue for last month and shows it as a measure. Using the SAMEPERIODLASTYEAR function, we can find the revenue for the month of January last year. From there, we can calculate the percentage change and understand how our revenues have changed.7. Let's practice!
With that, let's go ahead and dive back into our Superstore data and apply our window functions in new ways!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.