1. Average population
Hi there! Welcome back to the Time Series Analysis in Power BI course. In this session, I will be showing you a demo on how to apply window functions to your data.
In this scenario, I am working on analyzing global census and demographic data. Here I have a Power BI workbook loaded with a dataset with population numbers for every country worldwide since the beginning of 2021. You can see here that we have data for most days and countries, with the total count represented here. Our goal is to perform some analyses regarding the population size of each country, relative to the global average.
To start off, I am going to calculate the global average population, with the goal of showing it on our visual as a point of reference. To do so, I will create a new measure called “Global Average Population”.
Global Avg. Population = CALCULATE(AVERAGE(global_population[current_population]),
ALL(global_population[country]),
global_population[updated_datetime] = MAX(global_population[updated_datetime]))
To calculate this measure, we are going to start by using CALCULATE to compute the average of our population field. Now, since we want this measure to exist as a point of reference for all analyses, regardless of time and country, we need to pass a couple of filters. First, we want to ensure that this calculation applies to all of our countries using the ALL function. Secondly, we want to make sure that we are referring to the current global population, and we can do this by setting the relevant date to the MAX value. As you can see now, our average global population is just over 35 million people per country.
Now we can start diving into some country-specific analyses. I want to calculate a three-month rolling average population so we can see the general trend for each country. I will do this by using a few different time intelligence functions.
Rolling Average Population = CALCULATE(AVERAGE(global_population[current_population]),
DATESINPERIOD(global_population[updated_datetime],
LASTDATE(global_population[updated_datetime]), -3, MONTH))
First, I will again use the CALCULATE function and compute the AVERAGE of the population field. In this case, we will have a different filter that accounts for the date and the relevant window in which we apply the calculation. In DAX, this uses the DATESINPERIOD function, where we pass the dates to look over, the reference date (in this case, the LASTDATE of our column), and then we can specify the period in which to look. In our case, we want to look at the last three months.
With that calculation, we can now dive into some country-specific analyses. I will quickly create a slicer for our country, and plot the rolling average on a line chart, with the global average included as a reference point by a horizontal line on the y-axis. As I click through different countries, I can see where they lie from a relative size to the global average population.
This demo showed a quick example of calculating and using a rolling average window function in a specific scenario. Now, you will perform similar calculations on the Superstore dataset. Have fun!
2. Let's practice!