Week starting Tuesday returns wrong week 53

[Tropicaro]

Hi,

I need to report in weeks running Tuesday - Monday. In this forum I have found a way to add a calculated column to do just that. All weeks number perfectly, but something goes wrong at the end of the year. I'm getting week 53, where it should return week 52 or 1 (see highlighted cells).

The Coles Week is a column with manually entered data (just to check if the 'Coles Wk#' column returns the right value)

Any thoughts on how to solve this?

Thanks!

[v-yulgu-msft]

Hi @Tropicaro,

Take the first row and third row in above table as examples.

In Year 2017 which contains 365 days, the first week starts from '2017-01-01' to '2017-01-07', so, the last day of week 52 is '2017-12-30'. Then, the week number of '2017-12-31' that returned by dateadd('Date'[Sale Date],-2,DAY) should be 53.

In Year 2016, the first week starts from '2016-01-01' to '2016-01-02', so the last day of week52 is '2016-12-24'. The week number of '2016-12-30' that returned by dateadd('Date'[Sale Date],-2,DAY) should be 53.

Best regards,

Yuliana Gu

[Tropicaro]

Thank you very much, I understand.

However... we report in the weeks as mentioned in the Coles Week column. I wanted to avoid the manual entry, mainly because when I try to use this variable in my graphs they don't sort and I'm not able to apply a 'sort as'.

Is there any way to recreate the Coles Week column with a calculation? If that makes any sense?

[PattemManohar]

@Tropicaro Please provide the sample data (copiable format) to replicate your issue and also expected output as well.

[Tropicaro]

Hi Pattemmanohar,

Sorry, only back at work now. I have added a link to a copy of the file.

The Coles Week runs from Tuesday to Monday. I am looking for a formula based on column [Sales Date], that would recreate the week numbers of column [Coles Week]

https://mackaysmarketing-my.sharepoint.com/:u:/g/personal/carolien_mackaysmarketing_com_au/EQR4tCJ63...

Thank you so much!