I have created a column called visual date which takes a date and works out the week start date in order for me to show a weekly increment in my visual.
Everything seems to work fine, however, I noticed when you start a new month within the week it will mess up. I.e. Once January 2022 turns to February 2022, it calculates the week start date of 01/02/2022 as 03/03/22
DATE(YEAR([Date]),MONTH([Date]),DAY([Date] - WEEKDAY([Date],1) +1))
Does anyone know why this is happening? If so, what formula am I supposed to be using in order to work out the weekly increment correctly
Expected Output:
Dates between 01/02/22 - 05/02/22 the visual date column should be returning 30/01/22 for them
>Solution :
You could use 'Table'[Date] - WEEKDAY('Table'[Date],1) + 1.
Update: haven’t noticed in the first place, that you start with Sunday. Now its corrected.
Output:
Column2 is the new one, Column1 is with the old formula.