How to calculate week number starting from a specific day of the week by month

Published: 08 Feb 2024
Last Modified Date: 09 Feb 2024

Question

For date data with only certain days of the week, I want to set a week number for each month under the following conditions.
In the case of Monday-only data, the following conditions are used.
- The weekly period is 7 days starting on Monday, and the month in which Monday falls shall be the month of the week *The same week number shall be applied during the week
- The week containing the first Monday shall be number 1 in the week number of the month
User-added image

In the example below from year 2020 calendar, you want to set the week of 6th as the first week of the month since it's Monday, instead of the previous week starting on 1st.
User-added image

Environment

Tableau Desktop

Answer

Create the following calculated fields:
*in the case of Monday-only data

- Maximum week number of the previous month

DATEPART('week',DATEADD('day',-1,DATETRUNC('month',[Date])),'Monday')

Calculate the maximum week number of the previous month for "Date" field with Monday as the start of the week.

- Minimum week number for the current month

{ FIXED DATETRUNC("month",[Date]):MIN(DATEPART('week',[Date],'Monday'))}

Calculate the minimum week number for the current month's actual data for "Date" field with Monday as the start of the week.

- Week Number by Month

DATEPART('week',[Date],'Monday') - [ Minimum week number for the current month] +
IF [ Minimum week number for the current month] = [Maximum week number of the previous month] THEN 0 ELSE 1 END

With Monday as the start of the week, the week number is calculated for the "Date" field and a calculation is performed to determine what number it is in the current month.
1 is added depending on whether the week number of the first Monday matches the maximum week number of the previous month.