KNOWLEDGE BASE

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

Published: 08 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

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.

### Environment

• Tableau Desktop

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.