While perhaps not obviously useful on its own, there are many circumstances where having a sequential number across weeks, months, quarters, and years can facilitate other calculations. This recipe demonstrates how to construct a sequential week number across years.

To prepare for this recipe, perform the following steps:

1. Create a table using the following formula:

R08_Calendar = CALENDAR(DATE(2018,1,1),DATE(2022,12,31))

To implement this recipe, perform the following steps:

1. Create the following column in the R08_Calendar table:

SequentialWeek = 
    VAR __Date = 'R08_Calendar'[Date] // The working date, edit if column is not Date
    VAR __Year = YEAR(__Date) // Get the working date 
    VAR __Calendar = // Create calendar table with year and weeknum
        ADDCOLUMNS(
            ALL('R08_Calendar'), // Edit table name to match
            "__Year",
            YEAR('R08_Calendar'[Date]), // Edit table and column to match
            "__WeekNum",
            WEEKNUM('R08_Calendar'[Date]) // Edit table and column to match
        )
    VAR __FirstYear = MINX(__Calendar,[__Year]) // Determine the first year in our table
    VAR __WeekNum = // Get the current working week number
        MAXX(
            FILTER(
                __Calendar,
                [Date] = __Date
            ),
            [__WeekNum]
        )
    VAR __MaxWeeks = // Create a table of years and their max week numbers
        GROUPBY(
            __Calendar,
            [__Year],
            "__MaxWeek",
            MAXX(
                CURRENTGROUP(),
                [__WeekNum]
            )
        )
    VAR __Start = // Use __MaxWeeks table to get starting point for week number 
        SUMX(
            FILTER(
                __MaxWeeks,
                [__Year]<__Year
            ),
            [__MaxWeek]
        )
    VAR __Sequential = 
        IF(
            __Year=__FirstYear,
            __WeekNum, // If first year, then just the week number
            __Start + __WeekNum // Otherwise, __Start + current week number
        )
RETURN __Sequential

We start by getting our current working date and the year of that date. We then construct a calendar table, adding the year and week number as columns and return that to a variable named __Calendar. We use this __Calendar variable to find our first year, which is the starting year for our sequential week number. We store this year in the __FirstYear variable.

We now need to know the week number of our current working date within the year that it occurs. We do this by simply filtering our __Calendar variable down to our working date and grabbing the week number out of the __WeekNum column. We will use this later when calculating the sequential week number for weeks that occur after our first year (__FirstYear).

The next step is to find the starting week number for weeks that occur after our first year. We do this by first constructing a table called __MaxWeeks, grouping our __Calendar table by year (__Year) and placing the maximum week number for each year in a column called __MaxWeek. Once we have this table, we simply need to get the sum of these __MaxWeek values for every year that is less than our current working year. So, for example, if 2018 and 2019 each have 53 weeks, the starting point for the first week of 2020 is 53 + 53, or 106.

The last step is fairly straightforward. If our current working date is in the first year (__FirstYear), then our sequential number is simply the week number for that date in that year. However, for dates after our first year, we need to add our starting week (__Start) to our current week number (__WeekNum).

One drawback to the SequentialWeek recipe is that it does not account for weeks at the end of a year that do not contain a full seven days. For example, if we look at the end of 2018, we see that there are only two days in week 53 and that the first day of 2019 starts at week 54. Luckily, with a bit of extra code, we can fix this so that our sequential week counter always accounts for full seven-day weeks! To create this version of the recipe, create the SequentialWeek2 column in the R08_Calendar table using the code found in the R08_SequentialWeek2.txt file located in the GitHub repository. This code is almost exactly the same as before, except for the creation of three new variables, __Calendar1, __SubtractTable, and __Subtract, as well as a small change in the calculation of our __Sequential variable.

The goal here is to figure out how many of the years preceding our current working year do not have a full seven days in the last week of the year. We do this by first adding a column called __MaxWeek to our __Calendar table that stores the maximum week number in that year and storing this new table in the __Calendar1 variable. Because we are adding a column, we can use the row context and the EARLIER function to first filter our __Calendar table for all rows where the __Year column equals the __Year column of our current row. We then simply get the maximum value for the __WeekNum column within this filtered context.

Once we have this new column in our calendar table, now __Calendar1, we create a new table, __SubtractTable, that holds a table of years less than our current working year along with a column, __NumWeekDays, that holds the number of days in the last week of each of those years. Breaking the __SubtractTable calculation down, we apply FILTER to our __Calendar1 table such that we only filter the rows in the table where the years are less than our current working year and the week number equals our maximum week number for that year. We then group (GROUPBY) this filtered table by year and add in a count of the days within each group.

We can now simply count the rows in this new __SubtractTable variable that have __NumWeekDays less than seven to determine how many week numbers to subtract from our final sequential week number. We store this value in a variable called __Subtract and use this in our final __Sequential calculation for dates that are not in our first year (__FirstYear).

For more details regarding the functions in this recipe, refer to the following links:

• ADDCOLUMNS: https://docs.microsoft.com/en-us/dax/addcolumns-function-dax
• FILTER: https://docs.microsoft.com/en-us/dax/filter-function-dax
• GROUPBY: https://docs.microsoft.com/en-us/dax/groupby-function-dax
• EARLIER: https://docs.microsoft.com/en-us/dax/earlier-function-dax