Forum Discussion
Week starting Tuesday returns wrong week 53
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
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?
Tropicaro Please provide the sample data (copiable format) to replicate your issue and also expected output as well.
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]
Thank you so much!
