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)
