Search code examples
powerbidaxincrement

Why is my weekly increment not working using DAX

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))

enter image description here

enter image description here

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:

    enter image description here

    Column2 is the new one, Column1 is with the old formula.