Create an calendar table with only Year - Week numbers

[CornelisV]

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

[Greg_Deckler · Accepted solution]

@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

[CornelisV]

Thank you for your help!

[Deku]

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]

Hi @Deku ,

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

Best regards

Cornelis