How To Calculate Moving Average In Power Bi

How to Calculate Moving Average in Power BI

The moving average is a commonly used statistical calculation that helps to identify trends in data by smoothing out fluctuations. Power BI provides several ways to calculate moving averages depending on your specific requirements. Here’s an example of how to calculate a simple moving average using Power BI:

Data Preparation

Assuming you have a dataset with a date column and a numerical value column, follow these steps:

1. Load your dataset into Power BI.
2. Ensure that your date column is in the proper date format.
3. Create a new calculated column by right-clicking on your table and selecting “New Column”.
4. Enter a name for the column, such as “Moving Average”.
5. Use the following formula to calculate the moving average for a specific number of periods (e.g., 5 periods):

CALCULATE(
    AVERAGE([Value]),
    FILTER(
        ALL('Table'[Date]),
        'Table'[Date] <= EARLIER('Table'[Date])
        &&
        'Table'[Date] >= EARLIER('Table'[Date]) - 5
    )
)

In this formula, replace “Table” with the name of your table and “Value” with the name of your numerical value column.

Explanation

The formula uses the CALCULATE function to aggregate the average of the numerical value column. The FILTER function is used to create a filter context where only the previous 5 dates are considered for the moving average calculation.

Example

Let’s say you have a table called “Sales” with the following columns: Date (date format) and Revenue (numerical value). To calculate the moving average of the revenue over the previous 5 days, follow the steps mentioned above and substitute “Sales” for “Table” and “Revenue” for “Value” in the formula.

Once the calculation is done, you can visualize the moving average by adding it to a line chart or any other appropriate visualization in Power BI.