Moving Average calculations in Postgres and Power BI: A comparison

Moving Averages are calculated to take averages of data points within a particular time period, and data points for average calculations are shifted forward such as number of data points which are added forward is equal to the number of data points which are subtracted from the starting point of moving average. The duration for the moving average is same every time it is calculated, only the data points are shifted by some value. The new data points, after calculating moving average are then plotted on a chart to see the trend and changes over time. Moving Averages are also referred to as Rolling Averages.

For example: I have sales amount for months from January to May and I have to calculate moving average for 3 months to see changes in average values in these months. We have to first take the values of sales for first three months (January to March) and divide it by 3, second moving average would be calculated by taking away January sales value and starting from February and end in April, and calculating the average of sales for these 3 months, and so on.

Here is an illustration of how moving averages are calculated.

Now, let us see how we can calculate Moving averages in Postgres and Power BI.

MOVING AVERAGE CALCULATION IN POSTGRES USING PGADMIN:

We will use the dataset below to calculate moving average:

As you can see in the dataset, the dataset is not sorted in either ascending or descending order. For calculating the moving average, we have to first sort the dataset by date ascending.

Now, to calculate moving average, we have to define the number of data points for which the average needs to be calculated. The function in Postgres which is used to define window frame for those data points in the OVER function.

In this formula, we are creating a window frame for 3 months to calculate the average. In the first two rows, there are no preceding 3 values, so the formula is calculating average of 2 rows in second row and returning the sales value in the first row.

Using Postgres Graphic visualizer, we get the following chart for Amount and moving average of Amount.

Now, let's see how moving averages are calculated using DAX in Power BI.

The formula for calculating moving average is:

In this formula, we are first taking the average of the amount, which is the base for calculating Moving Averages. We are using CALCULATE function to provide filter context on how many values are to be taken at one time. Datesinperiod is a filter function which is the parameter of CALCULATE here and is providing the interval as Month, start date is taken as Max Date and end date is Max date -2 , so Average is calculated for a 3 month period in each time frame shifting forward by one month each time.

After plotting a line chart for Moving Average, it looks like this:

Moving Averages are important calculations in a company to understand the trend of a numerical figure, sales, in most cases.

Thanks for reading!