Power BI – Calendar Based Time Intelligence
By: David Rohlfs
Introduction
If you work with calendars in Microsoft Power BI and struggle with DAX calculations, the new Calendar Based Time Intelligence feature may solve your problems. Calendar Based Time Intelligence has added many new features to how date calculations are done in DAX. In this blog, we will discuss when you should use this feature, some common problems, and provide a walkthrough showing you how to set up your calendars.
This is a Preview Feature at the time of writing. If you need to turn on preview features, please refer to the linked blog, ‘Power BI Preview Features,’ below. Preview features are subject to change and may have complications or bugs.
When To Use It
The big advantage of Calendar Based Time Intelligence is that it allows us to define a calendar. This means that, in addition to the standard calendar elements (date, week, month, year), you can define custom aspects for your calendar. The best example is a Retail calendar (4,4,5). In a retail calendar, the dates are set to specific weeks of the year, removing remainder days in the year (days where the week they are in has a different calendar year). This allows for cleaner calculations and comparisons over dates. Previously, Power BI struggled to work effectively with custom calendars. The new time intelligence functions fix this because we are now able to define what our calendar year is.
There are a lot of things happening with this rollout! We have corrections for calendars without days, new week-to-date functions, performance enhancements, calendar organization upgrades, column mapping, and dependency enhancements.
Incomplete Calendar Correction
One of the issues that we run into with date/time intelligence in DAX is that you need to have complete date calendars. This means that the date field in your calendar has to have every date from start to finish, or else you will receive errors. This was problematic because complex equations were required to filter out days such as weekends, holidays, or bad weather days from calculations. With the new Calendar Based Time Intelligence, Power BI now allows you to define custom calendars and acts based on what is given in a table, not what is expected.
Week Based Calculations
This long-awaited feature addresses a significant gap in DAX functionality. One of the biggest downfalls (in my opinion) was that there had not been week-based calculations in DAX. This becomes frustrating when doing these calculations because if you wanted week, you had to set a filter in your equation that matched the weeks from your fact table to a specific week from your calendar. If you are familiar with the other YTD, QTD, and MTD calculations, then these WTD will fit right into your equation portfolio.
Here is an example of this equation. In this example, I am trying to get this year’s week-to-date sum of sales. The top image is an equation that I would have used with the filters, and the second image is an example using the new WTD function.
Calendar Organization Upgrades
In the past, an issue with calendars was that you needed to have multiple calendars (DAX calendar, Power Query, pulled from internet). This feature allows the report builder to essentially pull calendars out of a Master Calendar. Think of having a really big calendar that has Gregorian, Retail, and Fiscal calendars and being able to use all three of those at the same time. Calendar organization really works because you map your columns to a set calendar format with this preview feature.
Column Mapping
This is the only downfall that I see with this feature now. When you set up your new calendar, you need to map the columns in a specific way where they are kind of cascading. For example, if you have a week, month, quarter, and year, you would set up your calendar like this:
There isn’t much documentation on this yet, but the Validation will error if you do not have your calendar set correctly.
– Side Note: This was a common source of frustration for many users. While calendar based time intelligence seems to be very useful and helps with lots of issues, this negates the fact that we don’t need a standard calendar. If you want to relate one granularity to another, it needs to have this cascading relationship (granularity as year, quarter, month, week, and day).
Column Mapping Validation
In the calendar pop-out, you have a button in the top right that says “Validation.” This is essentially a quick check that you can run where Power BI looks at how your columns are mapped to your calendar and determines if there are any apparent issues.
Common Problems
Column Mapping
This issue is fairly simple but can be aggravating. You need to have good relational columns in your calendar. Essentially, you need cascading relationships between your date granularity (year, quarter, month, week, day). Depending on how your calendar needs to be set up, it can look similar to this. Notice that the higher level is year and it follows an obvious path to the smaller granularities.
DAX Functions Breaking
Be careful when you switch over using the calendar based time intelligence. This is mainly a DAX function, so it can break some of your measures or calculated columns if you add this feature later into building your report.
Walkthrough
There is a lot going on in this blog, so I am going to keep the walkthrough simple and just show you an example setting up a calendar and using it in one of the new WTD DAX functions!
- To start, we need to go to the model view. Choose this option on your left page navigation ribbon.
- Then go and find your Date Dimension in the Data Pane. Right click and choose Calendar Options.
- This will bring you to this pop-out. Start by choosing the New Calendar option.
- Add a Calendar Name. Make sure that this describes what type of calendar you use. If it’s a 445, then maybe add 445 to the name or Retail. Make it descriptive.
- Now you can start to map your columns. Only use the columns that you need to. This is a bit hard to explain now, but start with less and if you are missing something, try adding it in. Best Practice is to make these calendars have the least amount of columns possible. In this I am following my above example. No associated columns and down to the Week level.
- Next you need to validate your data. If your calendar needs work, it will give you this symbol with a brief description.
- Then hit Save and Close.
- You can always update, edit, or delete your calendar by going back to them in the model view under the Calendar Options. To add more calendars, you just keep adding them in the Calendar Options pop-out.
- Now let’s use that calendar in an equation. If we are setting up one of the new WTD DAX measures, then we need the calendar. In my equation I am saying:
WTD = WTD(SUM(‘Sales’[Sales Total]), ‘Calendar Name’)
Where [Sales Total] is our sales amount and “Calendar Base” is our new Calendar. Very simple, but you need the calendars to do it. This also affects other DAX because you can reference the calendar instead of the query.
Conclusion
This is all brand new, but Calendar Based Time Intelligence is obviously going to become a gamechanger for anyone who works on something other than the Gregorian calendar. This option is still in preview, so I hope that you can try it out and share your experience. If you enjoy this blog, please check out another from the link below!
Links Related to This Blog:
Comments
Leave a Reply
You must be logged in to post a comment.