Alternative Calendars

Few Power BI models are not concerned with dates. The proper way to handle time-based analysis in a Power BI model is to have a date or calendar table. It allows you to evaluate results not only by the periods of time you select, but also to compare results with the same result in the previous year, calculate year-to-date totals, or do other comparisons over time. In Chapter 1.3, Using DAX, the date table in a Power BI model was discussed. In Chapter 1.4, Context and Filtering, we covered DAX time intelligence functions that provide a wealth of time-based filter options.

The built-in DAX time intelligence functions assume that you use the common Gregorian calendar. As you know, this calendar organizes days into months with a variable number of days, quarters consisting of three months, and years containing four quarters. Many businesses do not work with the Gregorian calendar, but use a calendar that is primarily week-based instead. In such a calendar, days...

Week-based and Gregorian calendars

Let us first zoom in to what makes a week-based calendar fundamentally different from the Gregorian calendar, and what varieties exist in week-based calendars. We will choose one variety to work with in the remainder of this chapter.

What is a week-based calendar?

While in both week-based and Gregorian calendars, the day is the smallest unit, there is a huge difference in how days are grouped into larger units. While the Gregorian calendar knows about weeks, a week is not a proper hierarchical level in the calendar: most months are about 4½ weeks long, except for February (and not even in leap years). Even February usually starts somewhere in the middle of a week.

A week-based calendar uses the week as a proper hierarchical level from which all higher levels are defined. A week-based calendar does not have a month; instead, weeks are grouped into periods.

The advantage of a week-based calendar is that the end date of a period...

Creating a week-based calendar table

When you want to use a week-based calendar, you need to have an appropriate date table. In this section, we will construct a date table as a calculated table with DAX. We will give it the name ISO Date, and use the ISO 8601 standard.

You can download the model file for this chapter, 2.3 Alternative calendars.pbix, from https://github.com/PacktPublishing/Extreme-DAX/tree/main/Chapter2.3.

Setting up dates

Since the Gregorian calendar is the basis of all date calculations in DAX, we start the calendar formula as a date table based on the Gregorian calendar. The next step is adding columns to translate the Gregorian into a 4-4-5 calendar, followed by some necessary changes. We start with some variables to define the first and last year in the table:

ISO Date 1 = 
VAR StartYear = 2019
VAR EndYear = YEAR(TODAY())
RETURN
ADDCOLUMNS(
   CALENDAR(DATE(StartYear, 1, 1), DATE(EndYear, 12, 31)),
    "ISOWeek", WEEKNUM...

Time Intelligence calculations for week-based calendars

Now that we have created a 4-4-5 calendar table, we can start using this table to do time intelligence analysis. Instead of the common time intelligence calculations like year-to-date or month-to-date, we now need ISO-year-to-date, or period-to-date calculations. Since the standard DAX time intelligence functions are based on the Gregorian calendar, we will need to build our own calculations. To demonstrate the formulas, we will analyze the sales of the company QuantoBikes.

The Power BI model

QuantoBikes wants to monitor its sales. Since it uses a 4-4-5 calendar, all results need to be reported accordingly. These are the insights needed:

• Year-to-date sales: The cumulative sales within an (ISO) year.
• Year-on-year growth: Comparing sales of a day, week, period, quarter, or year to the results in the same time period of the previous year. As QuantoBikes works with a 4-4-5 calendar, the number of days in...

Keeping your report current

In most reports, you want to view the latest results. In a sales report, for instance, you may want to see results for the current and previous months. In a Power BI report, you can place a filter on the year and month to show results for that specific month. However, when a new month starts, each report user will have to change that filter manually to avoid looking at old data.

Power BI reports offer a feature to avoid having to do this: relative date filters. With a relative date filter, set in the reports Filter pane, you can set up a rule that is dynamic relative to the current date:

Figure 2.3.14: Relative date filters

While convenient, relative date filters have their limitations. For instance, you cannot set a relative date filter like "show the last two full months before today." Additionally, the relative date filters are based on the Gregorian calendar. When using a week-based calendar, you need to create an alternative...

Summary

In this chapter, you have learned how to implement time intelligence when your calendar looks different to the standard Gregorian calendar that a Power BI model assumes.

For time intelligence analysis, you need a specific date table containing columns corresponding to the hierarchical levels in your calendar. In this chapter, we have implemented this as a calculated table.

To do actual time intelligence calculations, meticulous filtering over the date table and the various columns in it is needed. In particular, calculating results for "the previous year" requires a lot of different calculations, depending on what selections are made in the query context. All this evokes a renewed appreciation for what the built-in DAX time intelligence functions accomplish!

We closed this chapter off with an alternative to relative date filters in Power BI reports that is more flexible and can handle selections in non-standard calendars as well.

The next chapter...