Forum Discussion

tl1234's avatar
tl1234
Icon for Helper I rankHelper I
8 months ago
Solved

Assign Week Number to a Single Month Across Multiple Years

Hello, I'm trying to make a comparison across multiple years of the number of people that stayed at our facility. The below snips show the query I made.     The below is my code.   ...
Need Help
  • Zanqueta's avatar
    Zanqueta
    8 months ago

    Hi  , tl1234 

    My recommended approache is instead of relying on month and week columns from your fact tables, create a Date Dimension with:

     

    • Continuous dates.
    • Columns for Year, Month, Month Name, Week Number, and optionally Year-Week (e.g., 2025-W01).
    • Mark this table as Date Table in Power BI.
      Then:
      • Relate your combined table to this Date Table using a proper date key.
      • Use the hierarchy from the Date Table in visuals (Year → Month → Week).
        This ensures consistent sorting and avoids the “ugly” misalignment.
        You can generate this Date Table in Power Query easily:
    let
    StartDate = #date(2022, 1, 1),
    EndDate = #date(2025, 12, 31),
    Dates = List.Dates(StartDate, Duration.Days(EndDate - StartDate)+1, #duration(1,0,0,0)),
    Table = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    AddColumns = Table.TransformColumns(Table, {{"Date", type date}}),
    AddYear = Table.AddColumn(AddColumns, "Year", each Date.Year([Date])),
    AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date])),
    AddMonthName = Table.AddColumn(AddMonth, "Month Name", each Date.MonthName([Date])),
    AddWeek = Table.AddColumn(AddMonthName, "Week Number", each Date.WeekOfYear([Date]))
in

     

    If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

    Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

Zanqueta's avatar
Zanqueta
Icon for Super User rankSuper User
8 months ago

Hi  , tl1234 

My recommended approache is instead of relying on month and week columns from your fact tables, create a Date Dimension with:

 

  • Continuous dates.
  • Columns for Year, Month, Month Name, Week Number, and optionally Year-Week (e.g., 2025-W01).
  • Mark this table as Date Table in Power BI.
    Then:
    • Relate your combined table to this Date Table using a proper date key.
    • Use the hierarchy from the Date Table in visuals (Year → Month → Week).
      This ensures consistent sorting and avoids the “ugly” misalignment.
      You can generate this Date Table in Power Query easily:
let
    StartDate = #date(2022, 1, 1),
    EndDate = #date(2025, 12, 31),
    Dates = List.Dates(StartDate, Duration.Days(EndDate - StartDate)+1, #duration(1,0,0,0)),
    Table = Table.FromList(Dates, Splitter.SplitByNothing(), {"Date"}),
    AddColumns = Table.TransformColumns(Table, {{"Date", type date}}),
    AddYear = Table.AddColumn(AddColumns, "Year", each Date.Year([Date])),
    AddMonth = Table.AddColumn(AddYear, "Month", each Date.Month([Date])),
    AddMonthName = Table.AddColumn(AddMonth, "Month Name", each Date.MonthName([Date])),
    AddWeek = Table.AddColumn(AddMonthName, "Week Number", each Date.WeekOfYear([Date]))
in

 

If this response was helpful in any way, I’d gladly accept a 👍much like the joy of seeing a DAX measure work first time without needing another FILTER.

Please mark it as the correct solution. It helps other community members find their way faster (and saves them from another endless loop 🌀.

tl1234's avatar
tl1234
Icon for Helper I rankHelper I
8 months ago

Hello,

Someone marked this as solved but it's not. I also had replied to you but for some reason that response is gone. I don't understand what you're recommending here/how to do it. Can you please provide more details?

Thank-you!