Calculate week start from date

Hi All,

Hopefully this is a easy one as want to be able to get the week start date , based on MTWTFSS so Monday is 1 and Sunday is 7, and this follow the logic of the function DayofWeek.

In my transform I am using the calculate transform with the function DayOfWeek function to get the day number from the date. I am using UK format dd/mm/yyyy.

In my sample data i have column ‘reported date’ that has the value 09/03/2024, a Saturday and day number is 6, so all good here. This new column is ‘DayofWeek’.

the next transform I then want to calculate the week start date , so use new calculate transform to a subtract, ‘reported date’ - ‘DayofWeek’, the result is 3/3/3024. But this is a sunday (7), it should be 04/03/2024 (1)??

Hmmm, so maybe my transform logic is wrong on how to get to the week start date?

Transform atatched with sample data.
week start date.transform (2.5 KB)

1 Like

maybe i just need to add a + 1 calculation ?

Sorry, I was working on something else and got mixed with the two, you need to minus 1 not add 1, if the date is within the week for which you want the start of week date.

Transform file.
week start date.transform (3.1 KB)

1 Like

yeah i did spot that but many thanks!