Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 , 20...
Need Help
  • HotChilli's avatar
    HotChilli
    5 years ago

    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's avatar
    Anonymous
    5 years ago

    This worked. Thanks a lot for help.

HotChilli's avatar
HotChilli
Community Champion
5 years ago

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's avatar
    Anonymous
    Not applicable
    5 years ago

    This worked. Thanks a lot for help.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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,