End of Year Wrong Week Number in Weekly Reporting

[tsteele]

Hello,

I took over a Power BI model from someone else and they're not available to answer my questions for another month. I'm fairly new to BI, but have completed an online course so I understand the basics, generally. At the start of the year, we discovered a unique issue that I'm not sure how to fix.

Since we report on a Weekly basis, It counted the last week of 2023 as Week 53 of the Year 2023, however most of the week took place in 2024.

The last recommendation I got from the former model manager was to change all of the Week 53 to Week 1.

This seemed to resolve the issue at first, but it ended up actually doubling the sales data for Week 1 of 2023, since the Week of 12/31/2023 is still considered part of the year 2023.

I've put in some forced work arounds, making 12/31/2023 count as part of Year 2024, but this offsets all the subsequent weeks.

What makes this more complex, is the Week Start is based off of Sundays, but we report on a POS Week which starts on Mondays. I mentioned changing them to match, and my supervisor (who is not acquainted with the BI backend) seemed reluctant to do so.

I don't see an option to attach a .pbix file to give an example of my date table, so here's the M code from the Advanced editor:

let
    // configurations start
    Today=Date.From(DateTime.LocalNow()), // today's date
    FromYear = 2009, // set the start year of the date dimension. dates start from 1st of January of this year
    ToYear=2024, // set the end year of the date dimension. dates end at 31st of December of this year
    StartofFiscalYear=7, // set the month number that is start of the financial year. example; if fiscal year start is July, value is 7
    firstDayofWeek=Day.Monday, // set the week's start day, values: Day.Monday, Day.Sunday....
    // configuration end
    FromDate=#date(FromYear,1,1),
    ToDate=#date(ToYear,12,31),
    Source=List.Dates(
        FromDate,
        Duration.Days(ToDate-FromDate)+1,
        #duration(1,0,0,0)
    ),
    #"Converted to Table" = Table.FromList(Source, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
    #"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "Date"}}),
    #"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([Date]), Int64.Type),
    #"Inserted Start of Year" = Table.AddColumn(#"Inserted Year", "Start of Year", each Date.StartOfYear([Date]), type date),
    #"Inserted End of Year" = Table.AddColumn(#"Inserted Start of Year", "End of Year", each Date.EndOfYear([Date]), type date),
    #"Inserted Month" = Table.AddColumn(#"Inserted End of Year", "Month", each Date.Month([Date]), Int64.Type),
    #"Inserted Start of Month" = Table.AddColumn(#"Inserted Month", "Start of Month", each Date.StartOfMonth([Date]), type date),
    #"Inserted End of Month" = Table.AddColumn(#"Inserted Start of Month", "End of Month", each Date.EndOfMonth([Date]), type date),
    #"Inserted Days in Month" = Table.AddColumn(#"Inserted End of Month", "Days in Month", each Date.DaysInMonth([Date]), Int64.Type),
    #"Inserted Day" = Table.AddColumn(#"Inserted Days in Month", "Day", each Date.Day([Date]), Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day", "Day Name", each Date.DayOfWeekName([Date]), type text),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Day Name", "Day of Week", each Date.DayOfWeek([Date],firstDayofWeek), Int64.Type),
    #"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Day of Year", "Month Name", each Date.MonthName([Date]), type text),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([Date]), Int64.Type),
    #"Inserted Start of Quarter" = Table.AddColumn(#"Inserted Quarter", "Start of Quarter", each Date.StartOfQuarter([Date]), type date),
    #"Inserted End of Quarter" = Table.AddColumn(#"Inserted Start of Quarter", "End of Quarter", each Date.EndOfQuarter([Date]), type date),
    #"Inserted Week of Year" = Table.AddColumn(#"Inserted End of Quarter", "Week of Year", each Date.WeekOfYear([Date], 6)),
    #"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([Date],firstDayofWeek), Int64.Type),
    #"Inserted Start of Week" = Table.AddColumn(#"Inserted Week of Month", "Start of Week", each Date.StartOfWeek([Date],firstDayofWeek), type date),
    #"Inserted End of Week" = Table.AddColumn(#"Inserted Start of Week", "End of Week", each Date.EndOfWeek([Date],firstDayofWeek), type date),
    FiscalMonthBaseIndex=13-StartofFiscalYear,
    adjustedFiscalMonthBaseIndex=if(FiscalMonthBaseIndex>=12 or FiscalMonthBaseIndex<0) then 0 else FiscalMonthBaseIndex,
    #"Added Custom" = Table.AddColumn(#"Inserted End of Week", "FiscalBaseDate", each Date.AddMonths([Date],adjustedFiscalMonthBaseIndex)),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom",{{"FiscalBaseDate", type date}}),
    #"Inserted Year1" = Table.AddColumn(#"Changed Type1", "Year.1", each Date.Year([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns1" = Table.RenameColumns(#"Inserted Year1",{{"Year.1", "Fiscal Year"}}),
    #"Inserted Quarter1" = Table.AddColumn(#"Renamed Columns1", "Quarter.1", each Date.QuarterOfYear([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns2" = Table.RenameColumns(#"Inserted Quarter1",{{"Quarter.1", "Fiscal Quarter"}}),
    #"Inserted Month1" = Table.AddColumn(#"Renamed Columns2", "Month.1", each Date.Month([FiscalBaseDate]), Int64.Type),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Month1",{{"Month.1", "Fiscal Month"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns3",{"FiscalBaseDate"}),
    #"Inserted Age" = Table.AddColumn(#"Removed Columns", "Age", each [Date]-Today, type duration),
    #"Extracted Days" = Table.TransformColumns(#"Inserted Age",{{"Age", Duration.Days, Int64.Type}}),
    #"Renamed Columns4" = Table.RenameColumns(#"Extracted Days",{{"Age", "Day Offset"}}),
    #"Added Custom1" = Table.AddColumn(#"Renamed Columns4", "Month Offset", each (([Year]-Date.Year(Today))*12)
+([Month]-Date.Month(Today))),
    #"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Month Offset", Int64.Type}}),
    #"Added Custom2" = Table.AddColumn(#"Changed Type2", "Year Offset", each [Year]-Date.Year(Today)),
    #"Changed Type3" = Table.TransformColumnTypes(#"Added Custom2",{{"Year Offset", Int64.Type}}),
    #"Added Custom3" = Table.AddColumn(#"Changed Type3", "Quarter Offset", each (([Year]-Date.Year(Today))*4)
+([Quarter]-Date.QuarterOfYear(Today))),
    #"Changed Type4" = Table.TransformColumnTypes(#"Added Custom3",{{"Quarter Offset", Int64.Type}}),
    #"Added Custom4" = Table.AddColumn(#"Changed Type4", "Year-Month", each Date.ToText([Date],"MMM yyyy")),
    #"Added Custom5" = Table.AddColumn(#"Added Custom4", "Year-Month Code", each Date.ToText([Date],"yyyyMM")),
    #"Changed Type5" = Table.TransformColumnTypes(#"Added Custom5",{{"Year-Month", type text}, {"Year-Month Code", Int64.Type}}),
    #"POS Week Start" = Table.AddColumn(#"Changed Type5", "POS Week Start", each Date.StartOfWeek([Date]), type date),
    #"Replaced Value" = Table.ReplaceValue(#"POS Week Start",53,1,Replacer.ReplaceValue,{"Week of Year"}),
    #"Changed Type6" = Table.TransformColumnTypes(#"Replaced Value",{{"Week of Year", Int64.Type}}),
    #"Modified Year to make Week 1 of 2024 reflect properly" = Table.AddColumn(#"Changed Type6", "Custom", each if [Date] = #date(2023, 12, 31) then 2024 else [Year]),
    #"Changed Type7" = Table.TransformColumnTypes(#"Modified Year to make Week 1 of 2024 reflect properly",{{"Custom", Int64.Type}}),
    #"Removed Columns1" = Table.RemoveColumns(#"Changed Type7",{"Year"}),
    #"Renamed Columns5" = Table.RenameColumns(#"Removed Columns1",{{"Custom", "Year"}}),
    #"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns5",{"Date", "Year", "Start of Year", "End of Year", "Month", "Start of Month", "End of Month", "Days in Month", "Day", "Day Name", "Day of Week", "Day of Year", "Month Name", "Quarter", "Start of Quarter", "End of Quarter", "Week of Year", "Week of Month", "Start of Week", "End of Week", "Fiscal Year", "Fiscal Quarter", "Fiscal Month", "Day Offset", "Month Offset", "Year Offset", "Quarter Offset", "Year-Month", "Year-Month Code", "POS Week Start"}),
    #"Added Conditional Column" = Table.AddColumn(#"Reordered Columns", "WeekofYear", each if [Date] = #date(2024, 1, 6) then 1 else [Week of Year]),
    #"Removed Columns2" = Table.RemoveColumns(#"Added Conditional Column",{"Week of Year"}),
    #"Renamed Columns6" = Table.RenameColumns(#"Removed Columns2",{{"WeekofYear", "Week of Year"}}),
    #"Changed Type8" = Table.TransformColumnTypes(#"Renamed Columns6",{{"Week of Year", Int64.Type}})
in
    #"Changed Type8"

Is there a way to fix the Week of Year to match the POS Week and have 12/31/2023 count as part of Week1 of 2024 without all of the weird work arounds?

[v-heq-msft · Accepted solution]

Hi @tsteele ,
To troubleshoot this, you can modify your date table to correctly calculate the "Week of Year" based on your POS week starting on Mondays. Here are some steps you can follow:
1.You need to create a new column that correctly identifies the week of the year based on a Monday start. You can use the function with the parameter to ensure the week starts on Monday.

#"Added Week of Year (POS)" = Table.AddColumn(#"Previous Step", "Week of Year (POS)", each Date.WeekOfYear([Date], Day.Monday))

2.To make December 31, 2023, part of Week 1 of 2024, you can create a conditional column that checks if the "Week of Year (POS)" is the last week of the year and if the year is 2023. If true, set the week to 1 and the year to 2024.

#"Adjusted Week and Year" = Table.TransformColumns(#"Added Week of Year (POS)", {
    {"Week of Year (POS)", each if _ = 53 and [Year] = 2023 then 1 else _},
    {"Year", each if [Week of Year (POS)] = 1 and [Date] > #date(2023, 12, 27) then 2024 else _}
})

3.After adjusting the "Week of Year (POS)" and "Year" columns, you can remove any temporary columns used for the calculations.

#"Removed Temporary Columns" = Table.RemoveColumns(#"Adjusted Week and Year", {"Week of Year", "Custom"})

For more information on working with dates and times in Power BI, you can refer to the following documentation:

DateTime functions - PowerQuery M | Microsoft Learn

Best regards,

Albert He

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[tsteele]

Hello Albert!

Thank you so much for replying. I adjusted the existing Week of Year column to be on Mondays instead of Sundays. Then, I attempted the rest of your solution, but it returned an error for all years. The previous years' Week 53s are also showing up as errors now, as well.

Do you know what could be causing this?

EDIT: This is the error I'm getting:

Expression.Error: We cannot apply field access to the type Number.
Details:
Value=2010
Key=Week of Year

It is the same for the Week 53s of previous years

EDIT 2: I replaced the brackets with "" and it worked!

So the final formula looked like this:

= Table.TransformColumns(#"POS Week Start", {
    {"Week of Year", each if _ = 53 and "Year" = 2023 then 1 else _},
    {"Year", each if "Week of Year" = 1 and "Date" > #date(2023, 12, 27) then 2024 else _}
})

Thank you so much for your help, Albert! I will mark you answer as the Solution!