Forum Discussion

CornelisV's avatar
CornelisV
Icon for Helper IV rankHelper IV
1 year ago
Solved

Create an calendar table with only Year - Week numbers

Dear all,

 

Could you please demonstrate me how to create an alternaticv Calender table with only Year and Week numbers:

So...

Year    Weeknumber

2024   51

2024   52

2025   01

2025   02

2025   03

.. and so on.

 

Best regards,

 

Cornelis

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    1 year ago

    CornelisV Try this:

     

    Calendar Table = 
  VAR __BaseTable = CALENDAR( DATE( 2024, 1, 1), DATE( 2025, 12, 31 ) )
  VAR __Table = 
    ADDCOLUMNS(
      __BaseTable,
      "Year", YEAR( [Date] ),
      "WeekNumber", WEEKNUM( [Date] )
    )
  VAR __Result = DISTINCT( SELECTCOLUMNS( __Table, "Year", [Year], "Weeknumber", [WeekNumber] ) )
RETURN
  __Result

     

4 Replies

  • Deku's avatar
    Deku
    Icon for Super User rankSuper User
    1 year ago

    I would suggest having a full calender table, but convert the weeks I assume you have in your fact table to date. These dates could be start of end of week. This will mean you can still use time intelligence functions

    • CornelisV's avatar
      CornelisV
      Icon for Helper IV rankHelper IV
      1 year ago

      Hi Deku ,

       

      Thank you. A full Calendar table is the best, but I have only data with weeknumers, not date 😐

       

      Best regards

      Cornelis

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    1 year ago

    CornelisV Try this:

     

    Calendar Table = 
  VAR __BaseTable = CALENDAR( DATE( 2024, 1, 1), DATE( 2025, 12, 31 ) )
  VAR __Table = 
    ADDCOLUMNS(
      __BaseTable,
      "Year", YEAR( [Date] ),
      "WeekNumber", WEEKNUM( [Date] )
    )
  VAR __Result = DISTINCT( SELECTCOLUMNS( __Table, "Year", [Year], "Weeknumber", [WeekNumber] ) )
RETURN
  __Result