Creating a Period Table

[NathanG]

I would like to create a Service Turnaround Time measure that shows the MTD, QTD, and YTD % change that can be calculated on a custom calender.

Making the calculations work according to the custom calender are the challenge since to my understanding time intelligence functions to not work with a custom calender. I have read Chapter 18 of Rob's book and it mentioned that you need a periods table but I do not understand how to go about setting up the foundation. I have also read his three posts on his website referring to creating your own time intelligence functions.

I currently have a production calender that is in the same format as a regular DimDates table except the dates of course are production dates. For example, on a regular calender the last week of December 2012 was 53, but on the production calender it is week 1.

Do I need both a production dates table and a period table? How do you fundamentally go about setting up custom calenders to create your own time intelligence functions?

Thank you for your help.

 

[MD610]

Basically the only difference between a standard date table and a custom date table is how the dates are categorized or grouped together. So, either way you need a column of contiguous dates. The differences will be in other columns of the Calendar.

For example, in a standard Calendar, Quarter 1 would include Jan, Feb, and Mar. As such you would have a field in your date table labeled Quarter and the value for every date in all 3 of those months would be Q1. However, if you were on a 4-4-5 Period setup, instead of standard quarters, then you would have a Period column and Jan, Feb, Mar, and Apr would all be labeled P1 in that field.

 

[MD610]

To elaborate more, I'm not sure if what you need is actually a custom calendar or if you are just shifting the timeframes on a standard calendar.

The reason to use a custom calendar is because the start and end dates of a period don't necessarily coicide with the actual start and end of a given month and periods can overlap more than one year. Do your periods coicide with actual months or are they based on weeks like 28 days or 35 days or something else?

Do you have more detail about your periods?

 

[marco.russo]

Nathan, I'm just writing an article about creating Time Intelligence functions (such as YTD, YOY, ...) with custom calendar (such as 4-4-5). I should complete and publish it within the upcoming week. I'll update this thread as soon as it is available.

 

[marco.russo]

I just published the Week-Based Time Intelligence DAX article, which contains calendar 4-4-5 time intelligence calculations, such us year-to-date (YTD), year-over-year (YOY) and so on. Please let me know if it contains formulas that are useful to you.

 

[NathanG]

Thank you both for your help. I have made some progress since the post.

Marco, this is great! I will spend some time looking this over. This is exactly what I need to understand.