It is about Time! A Power BI Calendars Walkthrough
It is rare to encounter any dataset that doesn’t have a time dimension.
although dealing with granularity as low as seconds has its uses. dealing with date hierarchies (day, month, quarter, year) is non-escapable in any respectable report.
Microsoft and Power BI know that, and there are plethora of DAX measures that are date specific to manipulate dates. I’m not here to talk about said measures, but to talk about something much more important:
Having an isolated Calendar Table in the form of a Lookup Table in your relational model.
if this makes you nod your head in agreement, then this post isn’t for you. this post is for users who are bashing their head because the SamePeriodLastYear() keeps throwing endless continuity errors in their measures.
Spoiler alert: it is because you are using your facts table date column, instead of , you guess it, a separate calendar table.
so let’s dive in shall we? there are six sections to this blogpost series:
- What is Calendar Table. Part1
- The benefit.Part1
- The setback. Part1
- My calendar Table Code (with Fiscal dates compatibility) Part2
- Examples of DAX: in the form of my favorite Time Measures. Part2
- The situational benefits of adding a second bridge calendar Part 3
What is a Calendar Table?
Calendar Table is lookup table that will reside in your relational model. it will consists of a primary key that is Calendar Date. each row will correspond to one day. Calendar table will be connected on said primary key to your date column of choice in your facts table. after that Calendar Table will have a bunch of useful columns that flag or group dates together. (i.e: calendar year, fiscal quarter, isWeekend?, monthName(sorted)..etc)
The Benefit
oh we will need a list for this one.
Fiscal Reporting
Does your company operate on a fiscal calendar that is different than normal calendars? most companies do. my company’s fiscal year start in November and ends in October. so our first quarter is Nov,Dec,Jan and so on. so what do I do when I get asked for cuts like the below example?
our facts table come with transaction date based on normal dates. as they should. changing a facts table date with a weird addition or subtraction should be a crime of all the hours it will be murdering.
the Calendar Table’s primary key is also normal date, and it connect to the transaction_date in the facts table in a 1-many relationship. I then use a bunch of Fiscal Columns within the calendar table to all my reporting purposes without breaking a sweat.
Automations
a good calendar table has column indexes that are numbered and that rotate dynamically. and can be used to automate your dashboards. what do I mean? let me know if you came across this problem:
you have a quarterly dashboard. it is beautiful, it is colorful, is insightful, it has all the data needs for your consumers and it is delieverd straight to their mailbox everyday at 2PM like clockwork. it has a date filter on all pages to match the current quarter.
BUT, every quarter, you have to go to that report and edit that filter to match the new quarter dates. no biggie, but when you have 10s and 100s of similar dashboard, it becomes a time sink quick. and here in this blog we hate needless grunt work. you have tried the advanced time filter. but they cant seem to get it right.
enters: the Calendar Table, armed with a Fiscal_Quarter_Offset column. in which the current quarter is always set to 0, the next quarter is 1, and previous quarter is -1. and so on. now all you have to do is set your filter on all pages to be equal to 0. and sail into the automated sunset.
further more, I add a more advanced column called Dashboard_Fiscal_Quarter_Offset, which stays at 0 throughout the first 4 days of the quarter. so that way the dashboard doesnt switch at midnight to next quarter when people still want to see the final numbers of the quarter the very next day.
This is just an example of the merrits of a calendar table in mix with DAX and different techniques. there is always a way with your trusty Calendar Table. at some point finance asked me to make a 3 previous years daily weighted average ratio to break down the current month’s forecast into daily cumulative chunks. kinda like a roadmap, but with the added twist that the weekends need to stay flat, still while increasing to match the final forecast of the month. even though previous years weekends dont fall on the same date as this year’s. go figure it out.
it was an interesting problem to solve. but I can say with confidence it would have been nigh impossible without my trusted Calendar Table. it has been three years and I havent even touched that report since, knowing full well it does exactly what it needs to do.
which leads me to my next point:
Continuity
DAX is a powerful language yet complex. similarly Time-based DAX is as fruitful as it is keniving. Power BI comes with a bunch of out of the box time measures. but even using them without a proper calendar, you will sooner or later encounter continuity errors.
this is when you are asking for year-over-year compares but you have filtered on two months that are not directly attached to each other. (ie: give me Jan and Apr $ and YoY% compare in the same table). you will get all sorts of error that tells you broke the timeline. it can be circumvented with masterful (and long) DAX. but it will be inefficient process time wise and honestly anyone who can write said masterful DAX would have connected a calendar table in 2 mins and moved on in the first place. why? because a Calendar Table is a 1-many single direction relationship with facts table. this means that it will not be filtered by anything except itself. making the Last Year $ measure as easy as using
Calculate([measure]. samePeriodLastYear(‘CalendarTable’[CalendarDate]))
in fact, year-over-year measures using calendar table is beginner level tip of the iceberg of the full potential of Calendar Table.
KPIs sync
do you have a forecast table or a KPI table? connecting these to a fact table is harmful and can lead to either headache or inaccuracies , or both. the proper way to connect a KPI table is to use lookup tables as bridges between it and the facts table. on any column that is a key. then in the visuals, use the column from the lookup bridge table to demonstrate your insights.
example: you have a monthly forecast spreadsheet by region. and you need to track your revenue against said forecast.
you shouldnt just connect forecast table to the facts table. to do this properly you’ll need a region table. connected to your forecast table in 1-many one-way relationship. and similarly connected to your facts table in 1-many one-way relationship. then you can use ‘Regions’[Region] column in your visuals. that way you avoid unintentional unrelated cross-filtering.
Dates in this case are not different. the monthly forecast file, is by month, and therefor will require a bridge to the facts table. that bridge is the Calendar Table (or a subsidiary that is connected to the Calendar Table).
Multi-facts table sync.
similar to the KPI sync, what if you have two facts tables in your dataset? you should use lookup tables as much as possible (like the Regions table in the example above). and Calendar Table is as crucial here as ever.
DAX
I said it before, but I really want to put emphasis on how game changing it is to have a calendar Table so you can masterfully manipulate your DAX measures.
The Setbacks of having a calendar Table
none.
ok fine, if I have to give you a setback. I guess the initial set up will take you a bit of time (10 mins). and that’s time you will never get back.(even though it is worth the investment)
in Part 2 I will give you my advanced Editor code so you can use my calendar (with some variation). that way you can connect it and get going even faster.
also some other cool Time related DAX tricks. stay tuned!