Forum Discussion
Label a date column for future weeks
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,
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.
3 Replies
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.