Custom Calendars in Power BI - New Preview Feature!

Custom calendars in Power BI, especially when working with fiscal years or unique retail patterns, have been a challenge for many. However, a new update has made it much easier. In this video, Allison Gonzalez, a Microsoft Certified Trainer at Pragmatic Works, walks us through the exciting new preview feature in Power BI: enhanced DAX time intelligence for custom calendars.

Setting Up the Custom Calendar in Power BI

To start using custom calendars, you first need to have a date table set up in your Power BI model. A date table is essential for time-related calculations such as month-to-date or year-to-date in Power BI. The video highlights the following steps:

• Ensure the date table is in place with no gaps or repeats, containing columns like day of the week, month number, year, quarter, etc.
• Ensure that the date table spans from the start date to the end date of your data.
• If you don’t have a date table, you can either get it from your organization's database or create it using Power Query or DAX.

Turning On the Preview Feature

The custom calendar feature is currently in preview, so you need to manually enable it. Here’s how:

• Navigate to File > Options and Settings > Options in Power BI.
• In the Global Section, look for the Preview Features and enable the enhanced DAX time intelligence feature.
• After enabling it, restart Power BI for the changes to take effect.

Once the feature is activated, you will be able to create multiple calendar options within your model.

Creating Custom Calendars

After enabling the preview feature, go to your date table and right-click to access the Calendar Options. You can now create a new calendar by:

• Giving your calendar a unique name for reference in DAX calculations (e.g., “Fiscal Calendar” or “Gregorian Calendar”).
• Mapping the categories (year, month, day, quarter) to columns in your date table.
• Validating data types and ensuring that columns match the expected values.

Using Custom Calendars in DAX

Once the calendars are set up, you can use them in your DAX calculations. For example, if you want to calculate year-to-date sales, you would use the newly created calendar in place of the default date table.

• The DAX function will reference the custom calendar instead of the date column in the original date table.
• You can easily swap between multiple calendars (e.g., fiscal or Gregorian) within your DAX functions.
• Calendars are stored behind the scenes, so they do not clutter the front end of the model.

Benefits of Custom Calendars in Power BI

This new feature in Power BI allows users to:

• Use different calendars for various types of reports (e.g., fiscal, retail, or week-based calendars).
• Simplify complex DAX calculations related to time intelligence.
• Save time by eliminating the need for complex manual date calculations.

It also offers greater flexibility and control over how your organization tracks and reports on time-related data.

Conclusion

The ability to create and manage custom calendars in Power BI is a game-changer for those dealing with fiscal years, retail cycles, and other non-standard time periods. As Power BI continues to evolve, features like this enhance its functionality, making it a powerful tool for business intelligence and reporting.

If you want to learn more about Power BI and its capabilities, Pragmatic Works offers a variety of on-demand training sessions. Be sure to check out the training platform and use code Allison40 for a 40% discount!

Don't forget to check out the Pragmatic Works' on-demand learning platform for more insightful content and training sessions on Power BI and other Microsoft applications. Be sure to subscribe to the Pragmatic Works YouTube channel to stay up-to-date on the latest tips and tricks.