Getting past 5 weeks from this year week one

[wanyunyang]

Hi all,

Is it possible to get the past 5 weeks from this year week one? So it will be: 2024 WK1, 2023 WK 52, 2023 WK 51, 2023 WK 50, 2023 WK49.

Thanks in advance!

[Oleg_Troyansky · Accepted solution]

Hi,

Yes, it is - that's the short answer to your question.

As to the obvious "how to" question, - developers typically add a running week number field, which increments by one for every week in the calendar, starting from the first week that exists in your calendar (assuming that you have a calendar table in your data set). Then you are independent from the year change - 5 weeks back will always be the current week number - 5.

Alternatively, you can base it on the days difference, since 5 weeks are always equal to 35 days - there is no ambiguity there. So, something like this should produce the first day of the week - 5:

WeekStart(today() - 35)

From here, you can calculate the rest - the year and the week number.

Cheers,