Toggle Overlapping Time Periods using the Calendar Table

Level: Intermediate.

I had a great question pass my way last week while I was at the Microsoft Business Application Summit. The problem presented by Matt (a different Matt, not me) was that he had around 200 measures already written, but wanted to be able to toggle the report pages between YTD and Full Year data.  For the sake of this article I will be using Year to Date (YTD) and Moving Annual Total (MAT) as the 2 toggle events.

Why Not Use a Switch Measure?

OK, why not use a switch measure to solve this problem I hear you say?  Well the issue is there are already 200 measures that are working just fine.  Using the switch measure approach would mean having to write new measures for EVERYTHING that needed to react to the switch measure slicer.  Instead Matt (the other Matt) had an idea to filter the calendar table based on the slicer selection, and have that impact ALL of the existing measures – great idea Matt!

But there is a problem with this approach.  Normally a slicer (in this case a slicer to toggle the filter on the calendar table) would have a single column with a Mutually Exclusive, Collectively Exhaustive (MECE) list of selection options.  As you can see in the image below, the Months highlighted in green need to be used in BOTH YTD and MAT.  So there is no way to use a single column in the Calendar table to solve this one.

How About 2 Columns in the Calendar Then?

Yes that would work.  You could create 2 new columns in the calendar table, one for a YTD flag and another for a MAT flag – that would work.  But of course the user would need to know they needed to select “Yes” to one and “No” to the other slicer – not a good experience.  So what to do?

Think About The Problem!

If you think about the problem above, each date (month in this case) can participate in both scenarios (YTD and MAT).  That sounds like a 1 to many relationship problem.  So once you understand that, it should become obvious that you will need a data table that contains the month records that belong to each use case.  The months that are in both scenarios will be duplicated in the new data table. The best way to explain it is by showing the final data model.

I have created a bridge table called AllMonths. This contains a distinct list of all the months in the calendar table in the format YYMM.

I also created a TimePeriod table that contains the list of months that map to YTD and also map to MAT.  The data in the TimePeriod table is shown below, and it is joined to the AllMonths table with a bi-directional filter as shown above.  Each month can participate in both period mappings and hence this is technically a data table.  The bi-directional filter means that any slicer on this table will push the filter onto the AllMonths table.  The AllMonths table will automatically filter the Calendar table.

As you can see with the data in the TimePeriod table above (sample data), the Moving Annual Total covers the periods July 2003 through June 2004, and the YTD period is Jan 2004 through June 2004.  In other words, all the periods YTD are also included in the MAT.  I am simulating the sample data showing the current period as being the end of June 2004.  In real life, you would use some sort of calculated table to automatically update on refresh to keep this table up to date for the current period.

The Final Result

The net result is as follows.  The Period slicer below comes from the TimePeriod table.  As long as you select one of the periods in this slicer, ALL of the measures in the entire data model will update to reflect that period of time without having to write custom measures that react to the slicer.

 

How to Make the Period Table Automatic

There was a good question in the comments from Uriel about just “how” the period table would auto update.  I didn’t provide a solution originally as Matt said it was working for him.  I had a play around with some DAX and came up with the following table DAX formula.

TestTable = 
    VAR datetoday = date(2004,7,5)  //replace this with TODAY() in real life
    VAR CompletedMonthID = CALCULATE(SELECTEDVALUE('Calendar'[MonthID]),'Calendar'[Date]=datetoday) -1
    VAR MATfrom = CompletedMonthID - 11
    VAR YTDStartDate = CALCULATE(STARTOFYEAR('Calendar'[Date]),'Calendar'[Date]=datetoday)
    VAR YTDMonthID = CALCULATE(SELECTEDVALUE('Calendar'[MonthID]),'Calendar'[Date]=YTDStartDate)
    VAR MATTable = ADDCOLUMNS(GENERATESERIES(MATfrom,CompletedMonthID),"Period","MAT")
    VAR YTDTable = ADDCOLUMNS(GENERATESERIES(YTDMonthID,CompletedMonthID),"Period","YTD")
    RETURN UNION(MATTable,YTDTable)

This code will generate a period table that should stay up to date each time the data is refreshed.  I have used a MonthID column in the calendar table instead of the YYMM column, so that would mean the bridge table would need to be switched to use that too.  Here is my workbook. Adventure Works Filter Calendar table

The approach above is relatively complex.  It would probably be slightly easier to add 2 new calculated columns for MAT and YTD.  These would be relatively easy to write using IF statements to determine which dates qualify for each.  Once these are in the calendar table, it would again be relatively easy to create a complete list of all dates as I have done above.  Something like this I guess.

TestTable2 =
    VAR MATTable = FILTER(ALL(Calendar[YYMM],Calendar[MAT]),Calendar[MAT]="MAT"))
    VAR YTDTable = FILTER(ALL(Calendar[YYMM],Calendar[YTD]),Calendar[YTD]="YTD"))
    RETURN UNION(MATTable,YTDTable)