How to create a rolling 12-month average in Power BI?

by

Here’s a question from a colleague:

“How can I create a rolling 12-month average measure to calculate employee turnover rate using Power BI?”

Rolling average measures in Power BI

Data Model

Let’s start by assuming the following data model:
Table: EmployeeTurnover
Columns:

  • Date (Date): Date of turnover data
  • TurnoverRate (Decimal): Monthly turnover rate

DAX for rolling measure in Power BI

  1. Create a Power BI Measure for the rolling 12-month average using the following DAX code:
Rolling 12-Month Avg Turnover = 
CALCULATE(
    AVERAGE(EmployeeTurnover[TurnoverRate]),
    DATESINPERIOD(
        EmployeeTurnover[Date],
        MAX(EmployeeTurnover[Date]),
        -12,
        MONTH
    )
)

Explanation

The DATESINPERIOD function creates a table of dates spanning the last 12 months from the latest date in our dataset. AVERAGEX then calculates the average turnover rate for these dates.

Create visuals

  1. Create a line chart:
  • Drag the ‘Date’ field to the Axis
  • Add the ‘Rolling 12-Month Avg Turnover’ measure to the Values
  1. Format the visual:
  • Set the X-axis type to ‘Date’
  • Adjust line color and thickness as desired
  • Add data labels if needed
  1. Create a card visual:
  • Add the ‘Rolling 12-Month Avg Turnover’ measure to the report
  • Format to display as a percentage with 2 decimal places

Troubleshooting

  1. Ensure your date column has continuous data for at least 12 months to prevent wrong results.
  2. Incorrect calculations – Verify that your TurnoverRate column contains numeric values and not percentages (e.g., 0.05 instead of 5%).
  3. Performance issues – For large datasets, consider using calculated columns or incremental refresh to optimize performance.
  4. Check if your Date column is recognized as a date type in Power BI. If not, use the ‘Format’ option in the modeling tab to set it correctly.

A possible enhancement to this analysis,could be to consider adding a month-over-month comparison or creating a forecast using Power BI’s built-in forecasting feature to predict future turnover rates based on historical trends.