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...