DAX PowerPivot Arbitrary Time Intelligence with Fact tables containing Future Data

One thing I love about PowerPivot is the Time Intelligence feature. Aspects of Time Intelligence appear non-intuitive at first, but with practice, it becomes clear. This is all very well if you have nice, clean Time Intelligence requirements, such as Year to Date, Month to Date, and so on. However, what happens if you have arbitrary or variable date handling requirements? What happens if these arbitrary requirements are complicated by the additional factor where your fact table contains future facts in addition to previous facts?

This blog focuses on handling arbitrary time periods where it’s not possible to use more standard Time Intelligence functionality in DAX. It also addresses the issue whereby the ‘rolling’ period needs to exclude future months in the calculation, since you need to know the rolling period from now, not from some future date. This activity has already been covered briefly on PowerPivotPro’s site.  so I would like to call out this post as a useful first step which helped me to get onto the road of arranging my own arbitrary time dimension in my environment, which I subsequently developed and enhanced for the rest of Step One. This helpful advice helped to set us on the road for implementing Time Intelligence in arbitrary environments.

However, this blog takes this idea and makes it into something different since the topic is covered specifically in terms of fact tables that contain future events.

The sequence of activities is as follows:

1. Create a month sequence number that starts at zero, being the earliest month, and increments until the latest month is reached.
2. Create calculated columns that help you to handle the arbitrary time intelligence periods by marking rows appropriately. For example, if the user wants to see the last 13 rolling months, then these rows can be identified and the calculated column can contain a relevant value e.g. ‘Last 13 Months’.
3. Create a calculated measure in order to retrieve the correct value in the calculated columns and display it in the Excel spreadsheet.

Here are the steps to set up the arbitrary time period. We use the AdventureWorks2008R2 database, which is available from CodePlex. The first step requires a bit of discussion since there are different ways of implementing this feature, and we settle on the best one.
We then move onto the second and third steps.

Step 1: Calculated Column to hold the month sequence number

The first step is to create a calculated column which contains a month sequence number that starts at zero, and increments with each month. This appears as follows:

Month Seq No =(FactInternetSales[OrderYear] – MINX(FactInternetSales,FactInternetSales[OrderYear])) * 12   + RELATED(DimDate[MonthNumberOfYear] )

However, what happens if your Fact table contains future data?  The issue here is that your MAX date could be in a future time, so the ’13 month’ period would start from the future date, not the current date.
In order to ensure that we capture the current date rather than future dates, we need to amend the ‘IF’ statement so that it looks at the existing month. For clarity, we can put this into another calculated column so you can see the difference.

This formula will take care of records where the date is in the next year or later:

=IF(  Year(TODAY()) < FactInternetSales[OrderYear], 0,   (FactInternetSales[OrderYear] – MINX(FactInternetSales,FactInternetSales[OrderYear])) * 12   + RELATED(DimDate[MonthNumberOfYear] )  )

The above fragment might help in the case where you wish to exclude records that occur in later years
What about the case where the records might occur later in the current year?
This condition could be added to the formula in the calculated column as follows:
sinc
=IF( MONTH(TODAY()) > RELATED(DimDate[MonthNumberOfYear])  && YEAR(TODAY() ) = FactInternetSales[OrderYear]   ||  Year(TODAY()) < FactInternetSales[OrderYear], 0,   (FactInternetSales[OrderYear] – MINX(FactInternetSales,FactInternetSales[OrderYear])) * 12   + RELATED(DimDate[MonthNumberOfYear] )  )

When we use the revised formula above, this means that the month is only assigned a sequence number if the year and month is less than, or equal to, the current month. This means that future records in the fact table are not included in the ‘Rolling 13 month’ since the sequence number is set to zero for these records. This ensures that the maximum value is the current month, which allows us to navigate more clearly.

Step 2: Calculated column to identify the arbitrary Time Intelligence

We can now set up a calculated column that provides arbitrary Time Intelligence information. The following formula takes the maximum Month Sequence Number, and marks the most recent 13 months as “Last 13 Months”. The 13 months previous to this set is marked as “Last 13-26 Months”. Other records are marked as “Not Relevant”. This is calculated in the following formula:

Month_13MonthPeriods =IF(  FactInternetSales[Month Seq No] >=  MAX(FactInternetSales[Month Seq No] ) – 13,  “Last 13 Months” , IF(  ( ( FactInternetSales[Month Seq No]   = MAX( FactInternetSales[Month Seq No]  ) – 26 )   ),  “Last 13-26 Months”, “Not Relevant”  )   )

Step 3: Calculated measure to pick out the arbitrary Time Intelligence

It is possible to set up a calculated measure that will filter out the rows for the last 13 months, and display only that data. Here is an example, that will display the amount if the data is within the time period of Last 24-48 months, otherwise it will return zero value. An example formula could be found here:

=CALCULATE(sum(FactInternetSales[SalesAmount]), filter(FactInternetSales, FactInternetSales[Month_24MonthPeriods] = “Last 24-48 Months” )  )   

I hope that this has been useful for someone! I look forward to your comments.