How To Calculate Rolling 12 Months In Power Bi

To calculate the rolling 12 months in Power BI, you can use a combination of DAX functions and measures. Here is a step-by-step explanation with examples:

1. Create a date table: Start by creating a date table in Power BI that includes all the dates within the desired range. You can also import an existing date table from a database or use the “Enter Data” option in Power BI.
2. Create a measure to calculate the rolling 12-month total: To calculate the rolling 12-month total, you need to create a measure that sums up the values for the selected period and the 11 preceding periods. Here’s an example of how you can create this measure:

Rolling 12 Months =
    VAR CurrentDate = MAX('DateTable'[Date])
    VAR StartDate = DATEADD(CurrentDate, -11, MONTH)
    VAR EndDate = CurrentDate
    RETURN CALCULATE(SUM('YourTable'[Value]), 'DateTable'[Date] >= StartDate && 'DateTable'[Date] <= EndDate)
  

In the above measure, ‘DateTable’ is the name of your date table, ‘YourTable’ is the name of your data table, and ‘Value’ is the name of the column that contains the values you want to sum up.

3. Use the rolling 12-month measure in visuals: Once you have created the rolling 12-month measure, you can use it in visuals such as line charts, bar charts, or tables to display the total for each date within the range. This will dynamically update as you filter or slice the data.

For example, if you have a line chart with the date on the x-axis and the rolling 12-month total on the y-axis, you will see a line that shows the cumulative total for each date within the selected range of the last 12 months.