rolling 12 month average

[powerbiexpert22]

i am trying to create rolling 12 months average using quick measure, what value i should enter in "period before" and "period after" as shown below?

[Shravan133 · Accepted solution]

1. Understanding "Period Before" and "Period After"

• Period Before: This specifies the number of periods before the current period that should be included in the calculation. For a rolling average, you want to include the past 12 months.

• Period After: This specifies the number of periods after the current period to include in the calculation. For a rolling average, this is usually set to 0, as you only want to include periods before and including the current period.

2. Setting Up a Rolling 12-Month Average

To calculate a rolling 12-month average, follow these steps:

1. Create a Quick Measure for Rolling Average:

   • In Power BI Desktop, go to your report view.
   • Right-click on the table where you want to add the measure and select New Quick Measure.
   • Choose Rolling Average from the list of Quick Measures.

2. Configure the Quick Measure:

   • Base Value: This should be the value you want to average, e.g., SUM(Sales[Amount]) or any other measure you are averaging.
   • Date Field: This should be your date field, e.g., Date[Date].

3. Set Period Values:

   • Period Before: Enter 11 to include the current month and the previous 11 months, totaling 12 months.
   • Period After: Enter 0 because you do not want to include any future periods in your rolling average.

Example Configuration

Assuming you are calculating a rolling average of sales amounts over 12 months:

• Base Value: SUM(Sales[Amount])
• Date Field: Date[Date]
• Period Before: 11 (for the 12 months including the current month)
• Period After: 0 (no future periods included)