Forum Discussion

tl1234's avatar
tl1234
Helper 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.

 

let
    Source = Table.NestedJoin(#"2022_weekly_pnights", {"month", "week", "Organization", "participant_type"}, #"2023_weekly_pnights", {"month", "week", "Organization", "participant_type"}, "2023_weekly_pnights", JoinKind.FullOuter),
    #"Renamed Columns" = Table.RenameColumns(Source,{{"Count", "2022_pnights"}, {"cost", "2022_cost"}}),
    #"Expanded 2023_weekly_pnights" = Table.ExpandTableColumn(#"Renamed Columns", "2023_weekly_pnights", {"month", "week", "Organization", "participant_type", "Count", "cost"}, {"2023_weekly_pnights.month", "2023_weekly_pnights.week", "2023_weekly_pnights.Organization", "2023_weekly_pnights.participant_type", "2023_weekly_pnights.Count", "2023_weekly_pnights.cost"}),
    #"Added Custom" = Table.AddColumn(#"Expanded 2023_weekly_pnights", "Custom", each if [month] = null then [2023_weekly_pnights.month] else if [2023_weekly_pnights.month] = null then [month] else [month]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"2023_weekly_pnights.month", "month"}),
    #"Renamed Columns1" = Table.RenameColumns(#"Removed Columns",{{"Custom", "month"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns1", "Custom", each if [week] = null then [2023_weekly_pnights.week] else if [2023_weekly_pnights.week] = null then [week] else [week]),
    #"Removed Columns1" = Table.RemoveColumns(#"Added Custom1",{"week", "2023_weekly_pnights.week"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "week"}}),
    #"Added Custom2" = Table.AddColumn(#"Renamed Columns2", "Custom", each if [Organization] = null then [2023_weekly_pnights.Organization] else if [2023_weekly_pnights.Organization] = null then [Organization] else [Organization]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Custom2",{"2023_weekly_pnights.Organization", "Organization"}),
    #"Renamed Columns3" = Table.RenameColumns(#"Removed Columns2",{{"Custom", "organization"}}),
    #"Added Custom3" = Table.AddColumn(#"Renamed Columns3", "Custom", each if [participant_type] = null then [2023_weekly_pnights.participant_type] else if [2023_weekly_pnights.participant_type] = null then [participant_type] else [participant_type]),
    #"Removed Columns3" = Table.RemoveColumns(#"Added Custom3",{"2023_weekly_pnights.participant_type", "participant_type"}),
    #"Renamed Columns4" = Table.RenameColumns(#"Removed Columns3",{{"Custom", "participant_type"}, {"2023_weekly_pnights.Count", "2023_pnights"}, {"2023_weekly_pnights.cost", "2023_cost"}}),
    #"Merged Queries" = Table.NestedJoin(#"Renamed Columns4", {"month", "week", "organization", "participant_type"}, #"2024_weekly_pnights", {"month", "week", "Organization", "participant_type"}, "2024_weekly_pnights", JoinKind.FullOuter),
    #"Expanded 2024_weekly_pnights" = Table.ExpandTableColumn(#"Merged Queries", "2024_weekly_pnights", {"month", "week", "Organization", "participant_type", "Count", "cost"}, {"2024_weekly_pnights.month", "2024_weekly_pnights.week", "2024_weekly_pnights.Organization", "2024_weekly_pnights.participant_type", "2024_weekly_pnights.Count", "2024_weekly_pnights.cost"}),
    #"Added Custom4" = Table.AddColumn(#"Expanded 2024_weekly_pnights", "Custom", each if [month] = null then [2024_weekly_pnights.month] else if [2024_weekly_pnights.month] = null then [month] else [month]),
    #"Removed Columns4" = Table.RemoveColumns(#"Added Custom4",{"month", "2024_weekly_pnights.month"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Removed Columns4",{{"Custom", "month"}}),
    #"Added Custom5" = Table.AddColumn(#"Renamed Columns5", "Custom", each if [week] = null then [2024_weekly_pnights.week] else if [2024_weekly_pnights.week] = null then [week] else [week]),
    #"Removed Columns5" = Table.RemoveColumns(#"Added Custom5",{"2024_weekly_pnights.week", "week"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Removed Columns5",{{"Custom", "week"}}),
    #"Added Custom6" = Table.AddColumn(#"Renamed Columns6", "Custom", each if [organization] = null then [2024_weekly_pnights.Organization] else if [2024_weekly_pnights.Organization] = null then [organization] else [organization]),
    #"Removed Columns6" = Table.RemoveColumns(#"Added Custom6",{"2024_weekly_pnights.Organization", "organization"}),
    #"Renamed Columns7" = Table.RenameColumns(#"Removed Columns6",{{"Custom", "organization"}}),
    #"Added Custom7" = Table.AddColumn(#"Renamed Columns7", "Custom", each if [participant_type] = null then [2024_weekly_pnights.participant_type] else if [2024_weekly_pnights.participant_type] = null then [participant_type] else [participant_type]),
    #"Removed Columns7" = Table.RemoveColumns(#"Added Custom7",{"2024_weekly_pnights.participant_type", "participant_type"}),
    #"Renamed Columns8" = Table.RenameColumns(#"Removed Columns7",{{"Custom", "participant_type"}, {"2024_weekly_pnights.Count", "2024_pnights"}, {"2024_weekly_pnights.cost", "2024_cost"}}),
    #"Merged Queries1" = Table.NestedJoin(#"Renamed Columns8", {"month", "week", "organization", "participant_type"}, #"2025_weekly_pnights", {"month", "week", "Organization", "participant_type"}, "2025_weekly_pnights", JoinKind.FullOuter),
    #"Expanded 2025_weekly_pnights" = Table.ExpandTableColumn(#"Merged Queries1", "2025_weekly_pnights", {"month", "week", "Organization", "participant_type", "Count", "cost"}, {"2025_weekly_pnights.month", "2025_weekly_pnights.week", "2025_weekly_pnights.Organization", "2025_weekly_pnights.participant_type", "2025_weekly_pnights.Count", "2025_weekly_pnights.cost"}),
    #"Added Custom8" = Table.AddColumn(#"Expanded 2025_weekly_pnights", "Custom", each if [month] = null then [2025_weekly_pnights.month] else if [2025_weekly_pnights.month] = null then [month] else [month]),
    #"Removed Columns8" = Table.RemoveColumns(#"Added Custom8",{"2025_weekly_pnights.month", "month"}),
    #"Renamed Columns9" = Table.RenameColumns(#"Removed Columns8",{{"Custom", "month"}}),
    #"Added Custom9" = Table.AddColumn(#"Renamed Columns9", "Custom", each if [week] = null then [2025_weekly_pnights.week] else if [2025_weekly_pnights.week] = null then [week] else [week]),
    #"Removed Columns9" = Table.RemoveColumns(#"Added Custom9",{"2025_weekly_pnights.week", "week"}),
    #"Renamed Columns10" = Table.RenameColumns(#"Removed Columns9",{{"Custom", "week"}}),
    #"Added Custom10" = Table.AddColumn(#"Renamed Columns10", "Custom", each if [participant_type] = null then [2025_weekly_pnights.participant_type] else if [2025_weekly_pnights.participant_type] = null then [participant_type] else [participant_type]),
    #"Removed Columns10" = Table.RemoveColumns(#"Added Custom10",{"2025_weekly_pnights.participant_type", "participant_type"}),
    #"Renamed Columns11" = Table.RenameColumns(#"Removed Columns10",{{"Custom", "participant_type"}}),
    #"Added Custom11" = Table.AddColumn(#"Renamed Columns11", "Custom", each if [organization] = null then [2025_weekly_pnights.Organization] else if [2025_weekly_pnights.Organization] = null then [organization] else [organization]),
    #"Removed Columns11" = Table.RemoveColumns(#"Added Custom11",{"2025_weekly_pnights.Organization", "organization"}),
    #"Renamed Columns12" = Table.RenameColumns(#"Removed Columns11",{{"2025_weekly_pnights.Count", "2025_pnights"}, {"2025_weekly_pnights.cost", "2025_cost"}, {"Custom", "organization"}}),
    #"Added Custom12" = Table.AddColumn(#"Renamed Columns12", "month_name", each if [month] = 1 then "January" else if [month] = 2 then "February" else if [month] = 3 then "March" else if [month] = 4 then "April" else if [month] = 5 then "May" else if [month] = 6 then "June" else if [month] = 7 then "July" else if [month] = 8 then "August" else if [month] = 9 then "September" else if [month] = 10 then "October" else if [month] = 11 then "November" else if [month] = 12 then "December" else "ERROR"),
    #"Reordered Columns" = Table.ReorderColumns(#"Added Custom12",{"month_name", "month", "week", "organization", "participant_type", "2022_pnights", "2022_cost", "2023_pnights", "2023_cost", "2024_pnights", "2024_cost", "2025_pnights", "2025_cost"}),
    #"Replaced Value" = Table.ReplaceValue(#"Reordered Columns",null,0,Replacer.ReplaceValue,{"2022_pnights", "2022_cost", "2023_pnights", "2023_cost", "2024_pnights", "2024_cost", "2025_pnights", "2025_cost"}),
    #"Added Custom13" = Table.AddColumn(#"Replaced Value", "average_pnights", each ([2022_pnights]+[2023_pnights]+[2024_pnights]+[2025_pnights])/4),
    #"Added Custom14" = Table.AddColumn(#"Added Custom13", "average_cost", each ([2022_cost]+[2023_cost]+[2024_cost]+[2025_cost])/4),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom14",{{"average_cost", Currency.Type}, {"2025_cost", Currency.Type}, {"2024_cost", Currency.Type}, {"2023_cost", Currency.Type}, {"2022_cost", Currency.Type}, {"week", Int64.Type}, {"month", Int64.Type}, {"average_pnights", type number}})
in
    #"Changed Type"

 

The problem I'm having is that we want the visual to be by month and week. However, week numbers are falling into multiple months making the visual look ugly and misrepresenting the data. The visual and how I created it are below.

 

 

Any suggestions on how I can fix this would be most appreciated.

 

Thanks!

Tanya

 

 

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 🌀.

6 Replies

  • v-achippa's avatar
    v-achippa
    Community Support
    8 months ago

    Hi tl1234,

     

    Thank you for reaching out to Microsoft Fabric Community.

     

    Thank you anilelmastasi and Zanqueta for the prompt response. 

     

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

     

    Thanks and regards,

    Anjan Kumar Chippa

    • v-achippa's avatar
      v-achippa
      Community Support
      8 months ago

      Hi @tl1234,

       

      We wanted to kindly follow up to check if the solution provided by the user's for the issue worked? or let us know if you need any further assistance.

       

      Thanks and regards,

      Anjan Kumar Chippa

  • Zanqueta's avatar
    Zanqueta
    Super 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
      Helper I
      7 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!

  • anilelmastasi's avatar
    anilelmastasi
    Super User
    8 months ago

    Hello tl1234 ,

     

    Weeks do not belong to months, they belong to dates. You should use WeekStartDate and take week after. You can add columns as week of start and week.

     

    If this solved your issue, please mark it as the accepted solution. ✅

    • tl1234's avatar
      tl1234
      Helper I
      8 months ago

      Hello,

      Sorry I don't understand your reponse. Can you please elaborate?

      Thank-you!