Forum

How do I calculate ...
 
Share:
Notifications
Clear all

How do I calculate the rolling average in Power BI using DAX? Provide an example with sample data and a detailed explanation


Posts: 92
Admin
Topic starter
(@sql-admin)
Estimable Member
Joined: 4 years ago

### Description:
Calculating the rolling average in Power BI is a powerful way to smooth out data and identify trends over a specific period. A rolling average, also known as a moving average, helps in analyzing data points by creating averages of different subsets of the complete dataset. This guide will walk you through how to calculate the rolling average in Power BI using DAX, with a detailed example and explanation.

### Answer:
To calculate the rolling average in Power BI using DAX, you can use a combination of the `AVERAGEX` function, the `DATESINPERIOD` function, and the `CALCULATE` function. This approach allows you to create a measure that calculates the average of data points over a specified period.

#### Step-by-Step Guide:

##### 1. Prepare Your Data
Ensure you have a dataset with a date column and the values you want to average. For this example, we'll use a simple sales table.

##### Sample Data:
Consider a sample sales table:

| Date | SalesAmount |
|------------|-------------|
| 2024-01-01 | 100 |
| 2024-01-02 | 150 |
| 2024-01-03 | 200 |
| 2024-01-04 | 250 |
| 2024-01-05 | 300 |
| 2024-01-06 | 350 |
| 2024-01-07 | 400 |

##### 2. Create the Rolling Average Measure
We will create a measure that calculates the 3-day rolling average for the `SalesAmount`.

```DAX
RollingAverage =
CALCULATE(
AVERAGEX(
DATESINPERIOD(
Sales[Date],
MAX(Sales[Date]),
-3,
DAY
),
Sales[SalesAmount]
)
)
```

#### Explanation:
- `DATESINPERIOD(Sales[Date], MAX(Sales[Date]), -3, DAY)`: This function generates a table containing the dates within the last 3 days, including the current date.
- `AVERAGEX(...)`: This function calculates the average of the `SalesAmount` over the dates returned by `DATESINPERIOD`.
- `CALCULATE(...)`: This function modifies the context of the calculation to include the rolling period.

##### 3. Add the Measure to Your Report
Add the `RollingAverage` measure to a table or line chart to visualize the 3-day rolling average of sales.

#### Example Calculation:
Using the sample data, the rolling average calculation for each date would be as follows:

| Date | SalesAmount | RollingAverage |
|------------|-------------|----------------|
| 2024-01-01 | 100 | 100 |
| 2024-01-02 | 150 | 125 |
| 2024-01-03 | 200 | 150 |
| 2024-01-04 | 250 | 200 |
| 2024-01-05 | 300 | 250 |
| 2024-01-06 | 350 | 300 |
| 2024-01-07 | 400 | 350 |

- **For 2024-01-01**: The rolling average is just the sales amount itself, as there are no preceding dates.
- **For 2024-01-02**: The rolling average is the average of the sales amounts for 2024-01-01 and 2024-01-02: `(100 + 150) / 2 = 125`.
- **For 2024-01-03**: The rolling average is the average of the sales amounts for 2024-01-01, 2024-01-02, and 2024-01-03: `(100 + 150 + 200) / 3 = 150`.
- **For 2024-01-04 and beyond**: The rolling average is calculated over the last 3 days.

#### Additional Customizations:
1. **Adjust the Rolling Period**:
You can adjust the rolling period by changing the `-3` parameter in the `DATESINPERIOD` function to any other number of days, weeks, or months as needed.

```DAX
RollingAverage7Days =
CALCULATE(
AVERAGEX(
DATESINPERIOD(
Sales[Date],
MAX(Sales[Date]),
-7,
DAY
),
Sales[SalesAmount]
)
)
```

2. **Handling Missing Data**:
Ensure that your date table is complete and has continuous dates to avoid gaps in the rolling average calculation. You can join your sales table with a complete date table to handle missing dates.

3. **Conditional Formatting**:
Use conditional formatting to highlight significant changes in the rolling average. This can help users quickly identify trends and anomalies in the data.

4. **Visualization Enhancements**:
Add a line chart to visualize the rolling average over time. You can also overlay the original sales data to compare the actual values with the smoothed rolling average.

By following these steps and additional customizations, you can effectively calculate and visualize the rolling average in Power BI, providing valuable insights into your data trends.

Leave a reply

Author Name

Author Email

Title *

 
Preview 0 Revisions Saved
Share: