Benefits of Dynamic Rolling Twelve Month Dimension in Tabular Models
Summary: This article describes how to automate dynamic rolling time periods (i.e. rolling 12, trailing twelve months) as a filterable dimension using DAX (Data Analysis Expressions).
Dynamic rolling periods dimension in tabular models removes manual maintenance steps and eliminates human error in reports that show trends. After implementing this method in production, we were able to cut the monthly reports suite refresh time by two hours while entirely eliminating the potential for human error.
Authors: Derek Daniels, Business Intelligence Consultant, and Sergei Gundorov (Microsoft)
Technical Reviewers: Josh Caplan, Program Manager, SQL Server BI (Microsoft)
Technical Editor: Heidi Steen (Microsoft)
Published: April 2014
Applies to: SQL Server (2012 & 2014) SSAS Tabular, Excel PowerPivot & Power View