Problem with WeekNum ( Date Table)

[Alecsen]

Hello all,

Please help me out.

I have a big data set that worked perfectly from last year, this year I have one problem with the WeekNum : 

1. I am trying to have the "First week start of the year" 27.12.2021 - Week 53-2021  and after on "First week start of the year" 03.01.2022 - Week 1 2022
2. After that to have the week number on the same value and the YearWeek , but is strange because i get differently results, i tried with ISOweeks and any other tips but i keep get stuck .

Here is a sample picture : 

Any help or suggestions is apreciated, even a replace or IF formula ; 

[KNP]

Hi @Alecsen - are you able to post the code you used to build this date table? It will make it easier for us to help you.

[Alecsen]

Hello,

I have build the data table as a query  :

let fnDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
let
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
ChangedType = Table.TransformColumnTypes(TableFromList,{{"Column1", type date}}),
RenamedColumns = Table.RenameColumns(ChangedType,{{"Column1", "Date"}}),
InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]),type text),
InsertQuarterNum = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date])),
InsertQuarter = Table.AddColumn(InsertQuarterNum, "Quarter", each "Q" & Number.ToText([Quarter Num])),
InsertMonth = Table.AddColumn(InsertQuarter, "Month Num", each Date.Month([Date]), type text),
InsertStartOfMonth = Table.AddColumn(InsertMonth, "StartOfMonth", each Date.StartOfMonth([Date]), type date),
InsertEndOfMonth = Table.AddColumn(InsertStartOfMonth, "EndOfMonth", each Date.EndOfMonth([Date]), type date),
InsertDay = Table.AddColumn(InsertEndOfMonth, "DayOfMonth", each Date.Day([Date])),
InsertDayInt = Table.AddColumn(InsertDay, "DateInt", each [Year]*10000 + [Month Num]*100 + [DayOfMonth]),
InsertMonthName = Table.AddColumn(InsertDayInt, "Month", each Date.ToText([Date], "MMMM", Culture), type text),
InsertShortMonthName = Table.AddColumn(InsertMonthName, "Month short", each Date.ToText([Date], "MMM", Culture), type text),
InsertCalendarMonth = Table.AddColumn(InsertShortMonthName, "Month Year", each [Month short]& " " & Number.ToText([Year]),type text),
InsertCalendarQtr = Table.AddColumn(InsertCalendarMonth, "Quarter Year", each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Num", each Date.DayOfWeek([Date])),
InsertDayName = Table.AddColumn(InsertDayWeek, "Weekday", each Date.ToText([Date], "dddd", Culture), type text),
InsertShortDayName = Table.AddColumn(InsertDayName, "Weekday short", each Date.ToText([Date], "ddd", Culture), type text),
InsertWeekEnding = Table.AddColumn(InsertShortDayName , "EndOfWeek", each Date.EndOfWeek([Date]), type date),
InsertWeekNumber= Table.AddColumn(InsertWeekEnding, "Week Num", each Date.WeekOfYear([Date])),
InsertMonthWeekNumber= Table.AddColumn(InsertWeekNumber, "WeekOfMonth Num", each Date.WeekOfMonth([Date])),
InsertMonthnYear = Table.AddColumn(InsertMonthWeekNumber,"Month-YearOrder", each [Year]*10000 + [Month Num]*100),
InsertQuarternYear = Table.AddColumn(InsertMonthnYear,"Quarter-YearOrder", each [Year]*10000 + [Quarter Num]*100),
ChangedType1 = Table.TransformColumnTypes(InsertQuarternYear,{{"Quarter-YearOrder", Int64.Type},{"Week Num", Int64.Type},{"WeekOfMonth Num", Int64.Type},{"Quarter", type text},{"Year", type text},{"Month-YearOrder", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfMonth", Int64.Type}, {"Month Num", Int64.Type}, {"Quarter Num", Int64.Type}, {"Weekday Num", Int64.Type}})
in
ChangedType1
in
fnDateTable

[KNP]

I'm not sure I'm understanding 100% but I've edited your function to add ISOWeekNumber and ISOYear.

Let me know if this works...

let
  fnDateTable = (StartDate as date, EndDate as date, optional Culture as nullable text) as table =>
    let
      DayCount = Duration.Days(Duration.From(EndDate - StartDate)) + 1,
      Source = List.Dates(StartDate, DayCount, #duration(1, 0, 0, 0)),
      TableFromList = Table.FromList(Source, Splitter.SplitByNothing()),
      ChangedType = Table.TransformColumnTypes(TableFromList, {{"Column1", type date}}),
      RenamedColumns = Table.RenameColumns(ChangedType, {{"Column1", "Date"}}),
      InsertYear = Table.AddColumn(RenamedColumns, "Year", each Date.Year([Date]), type text),
      InsertQuarterNum = Table.AddColumn(InsertYear, "Quarter Num", each Date.QuarterOfYear([Date])),
      InsertQuarter = Table.AddColumn(
        InsertQuarterNum,
        "Quarter",
        each "Q" & Number.ToText([Quarter Num])
      ),
      InsertMonth = Table.AddColumn(InsertQuarter, "Month Num", each Date.Month([Date]), type text),
      InsertStartOfMonth = Table.AddColumn(
        InsertMonth,
        "StartOfMonth",
        each Date.StartOfMonth([Date]),
        type date
      ),
      InsertEndOfMonth = Table.AddColumn(
        InsertStartOfMonth,
        "EndOfMonth",
        each Date.EndOfMonth([Date]),
        type date
      ),
      InsertDay = Table.AddColumn(InsertEndOfMonth, "DayOfMonth", each Date.Day([Date])),
      InsertDayInt = Table.AddColumn(
        InsertDay,
        "DateInt",
        each [Year] * 10000 + [Month Num] * 100 + [DayOfMonth]
      ),
      InsertMonthName = Table.AddColumn(
        InsertDayInt,
        "Month",
        each Date.ToText([Date], "MMMM", Culture),
        type text
      ),
      InsertShortMonthName = Table.AddColumn(
        InsertMonthName,
        "Month short",
        each Date.ToText([Date], "MMM", Culture),
        type text
      ),
      InsertCalendarMonth = Table.AddColumn(
        InsertShortMonthName,
        "Month Year",
        each [Month short] & " " & Number.ToText([Year]),
        type text
      ),
      InsertCalendarQtr = Table.AddColumn(
        InsertCalendarMonth,
        "Quarter Year",
        each "Q" & Number.ToText([Quarter Num]) & " " & Number.ToText([Year]),
        type text
      ),
      InsertDayWeek = Table.AddColumn(InsertCalendarQtr, "Weekday Num", each Date.DayOfWeek([Date])),
      InsertDayName = Table.AddColumn(
        InsertDayWeek,
        "Weekday",
        each Date.ToText([Date], "dddd", Culture),
        type text
      ),
      InsertShortDayName = Table.AddColumn(
        InsertDayName,
        "Weekday short",
        each Date.ToText([Date], "ddd", Culture),
        type text
      ),
      InsertWeekEnding = Table.AddColumn(
        InsertShortDayName,
        "EndOfWeek",
        each Date.EndOfWeek([Date]),
        type date
      ),
      InsertWeekNumber = Table.AddColumn(InsertWeekEnding, "Week Num", each Date.WeekOfYear([Date])),
      InsertISOWeekNumber = Table.AddColumn(
        InsertWeekNumber,
        "ISO Week Number",
        each
          if Number.RoundDown(
            (Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
          )
            = 0
          then
            Number.RoundDown(
              (
                Date.DayOfYear(#date(Date.Year([Date]) - 1, 12, 31))
                  - (Date.DayOfWeek(#date(Date.Year([Date]) - 1, 12, 31), Day.Monday) + 1)
                  + 10
              )
                / 7
            )
          else if (
            Number.RoundDown(
              (Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
            )
              = 53 and (Date.DayOfWeek(#date(Date.Year([Date]), 12, 31), Day.Monday) + 1 < 4)
          )
          then
            1
          else
            Number.RoundDown(
              (Date.DayOfYear([Date]) - (Date.DayOfWeek([Date], Day.Monday) + 1) + 10) / 7
            )
      ),
      InsertISOYear = Table.AddColumn(
        InsertISOWeekNumber,
        "ISO Year",
        each Date.Year(Date.AddDays([Date], 26 - [ISO Week Number]))
      ),
      InsertMonthWeekNumber = Table.AddColumn(
        InsertISOYear,
        "WeekOfMonth Num",
        each Date.WeekOfMonth([Date])
      ),
      InsertMonthnYear = Table.AddColumn(
        InsertMonthWeekNumber,
        "Month-YearOrder",
        each [Year] * 10000 + [Month Num] * 100
      ),
      InsertQuarternYear = Table.AddColumn(
        InsertMonthnYear,
        "Quarter-YearOrder",
        each [Year] * 10000 + [Quarter Num] * 100
      ),
      ChangedType1 = Table.TransformColumnTypes(
        InsertQuarternYear,
        {
          {"Quarter-YearOrder", Int64.Type},
          {"Week Num", Int64.Type},
          {"WeekOfMonth Num", Int64.Type},
          {"Quarter", type text},
          {"Year", type text},
          {"Month-YearOrder", Int64.Type},
          {"DateInt", Int64.Type},
          {"DayOfMonth", Int64.Type},
          {"Month Num", Int64.Type},
          {"Quarter Num", Int64.Type},
          {"Weekday Num", Int64.Type}
        }
      )
    in
      ChangedType1
in
  fnDateTable

If this is what you're looking for, you'll need to take into account anything you're doing with "year" and consider using "ISO year".

For reference:

https://datacornering.com/how-to-calculate-iso-week-number-in-power-query/

https://datacornering.com/how-to-calculate-iso-year-in-power-query/

[v-rongtiep-msft]

Hi @Alecsen ,

I have create a simple sample. Please refer to see if it helps you.

wekkday_ = WEEKDAY('Table'[Date],2)

weeknumber = WEEKNUM('Table'[Date],2)

YearWee = YEAR('Table'[Date])&"-"&'Table'[weeknumber]

_First start week of the year = CALCULATE(MIN('Table'[Date]),FILTER(ALL('Table'),'Table'[weeknumber]=SELECTEDVALUE('Table'[weeknumber])))

If I have misunderstood your meaning, could you provide your pbix file without privacy information and desired output.

Best Regards

Community Support Team _ Polly

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

[Alecsen]

Hello @v-rongtiep-msft ,

Thank for your help, bassicly is somethin that you describe . But as I can see in the previous report the value days of the week 53 in your screen is 5 days and after the first of the year Week 1 has only one day. 

I would like to have week 53 as week 1 bassicly with 7 days .

[mahoneypat]

Please see if this article is helpful.

445 Calendar with 53-Week Years – Hoosier BI

Pat

[KNP]

@Alecsen,

Check out this post...

https://community.powerbi.com/t5/Desktop/Incorrect-week-number-at-year-end-beginning/td-p/1474033

I think you'l find what you need in there.

[Alecsen]

@KNP I trying without any help. I have the same results. 

[Alecsen]

@amitchandak any suggestions?