Rolling 12 Months Chart with PowerPivot

Once again, work has provided me with a great article topic!  A request recently came across my desk to create a rolling 12 & 24 months average.

Here is how to do it:

First thing is first: You will need to have the “Last Updated” Table&Column in your Model.

 If you can connect to a SQL Server database, I create this by adding a table "Last Updated" with the following SQL as source (This also tells the users how new the data is):

Select GetDate() as ‘Last Updated'

This method is preferred to simply using the Today() formula in a linked table because your source data may not be refreshed daily.  For example, we have some financial dashboards that are only refreshed once per month on the first day of the month.

Next, create a calculated measure on the "Last updated" table with the following formula:

LastUpdatedMeasure:=sum([Last Updated])

Next, on your Fact table that you are going to use to create the rolling average (or whatever rolling metric you desire), add the following Calculated Measures to determine start and end dates for the rolling year ([last updated date] - [12 months]):

RollingYearEnd:=EOMonth([LastUpdatedMeasure],-1)

RollingYearBegin:=1+edate([RollingYearEnd],-12) - You can make this 2 years by changing "-12" to "-24".

Finally, use the following calculate formula to run whatever calculation you are looking for within that rolling year. This is the measure you will use as the “Value” on your chart or pivot table.

The formula below has multiple parts that I will explain:

=Calculate(expression, filter1, filter2..)

=Average(Column)

=Filter(TableName, TableName[Filter Criteria])

"&&" - This allows you to use an "AND" in your Filter Criteria.  Example: Event Date is >= RollingYearBegin "AND" Event Date is <= RollingYearEnd  - This ensures your data is between the start and end date. You can also include a column to qualify the event to be included by simply adding another "&&"

RollingYear_Avg:=calculate(average([ColumnToBeAveraged]),filter(FactTableName,FactTableName[EventDateColumn]>=[RollingYearBegin]&&FactTableName[EventDateColumn]<=[RollingYearEnd]&&FactTableName[QualifyingEventColumn]="Y"))

The final part of the formula &&FactTableName[QualifyingEventColumn]="Y" was required in my case because not all of the rows in my dataset qualified to be included in the average.  This is not required to make this formula work.

So, now that you are an expert in rolling year formulas, go out and impress the world!