Label a date column for future weeks

[Anonymous]

Hi , I am trying to label a date column for future weeks. So if today is 1/29/2021, the "label column" should say Week 17 for any date in "promise date" Col which is between 23 May, 2021- 29 May , 2021 (which is 17th week from today). Week 6 for any date in betweeen 7 Mar, 2021 - 13 Mar, 2021.

As the present date changes , it should dynamically be able to calculate which dates falls in Week 1- Week 18. The work week for current report purposes is Sunday - Saturday.

Any suggestions would be of great help.

Thanks a lot in Advance for Help,

[HotChilli · Accepted solution]

If you create a Date table (with a CALENDAR or CALENDARAUTO statement - you can limit this from current date to 18 weeks on, if required) , you can create a weekNo column with something like this:

WeekNo = WEEKNUM(TableDates[Date]) - WEEKNUM(NOW())

That'll give the offset from the WEEKNUM of each date value.

Link the Date table to your Fact table.

The Date table will recalculate when the data is refreshed

Please test at your side because I threw this together quickly.

[HotChilli · Accepted solution]

If you create a Date table (with a CALENDAR or CALENDARAUTO statement - you can limit this from current date to 18 weeks on, if required) , you can create a weekNo column with something like this:

WeekNo = WEEKNUM(TableDates[Date]) - WEEKNUM(NOW())

That'll give the offset from the WEEKNUM of each date value.

Link the Date table to your Fact table.

The Date table will recalculate when the data is refreshed

Please test at your side because I threw this together quickly.

[Anonymous]

Is it possible to have just one date - first date for each week number? Right now all available dates for the week is getting listed. For instance , Week 1 has 7th Feb and 8th Feb. Week 2 has 14 feb and 15 feb. 

Is it possible to have only 7th Feb and 14th Feb and so on respectively.Thanks,

[Anonymous · Accepted solution]

This worked. Thanks a lot for help.