Solved: Problems with an week index in Power Query

hansreivers · ‎08-28-2024

Hi All,

I've an issue creating a week index. This week index is a bit abnormal...

We have the normal year of the date and the ISO week of the date. But, when I have for example wednesday 01-01-20, this is week index is -243, but when I go to 31-12-19, the index should be -244 (because, I want to follow, normal year / ISO week)

Here, there 01-01-23 should be -86 instead of -87. The index should "follow" Jaar(datum) + HRE ISO week...

Is this possible to create? Please help 🙂

m_dekorte · ‎09-10-2024

Hi @hansreivers,

I've updated the Dates table, it now includes the Custom ISO Week and a CurrWeekOffset column. Validate it to confirm it meets your requirements.

Use your offset columns for Time Intelligence needs.

See DM.

View solution in original post

hansreivers · ‎09-11-2024

Wow... this is next level... !

m_dekorte · ‎08-28-2024

Hi @hansreivers,

Are you referring to an ISO-8601 calendar?

Because none of your images align...

And here

I'm sure it can be done however keep in mind that overruling ISO impacts weeks at both the start as well as at the end of a year, you'll end up with weeks greater than or less than 7 days on either side of that boundary.

Instead consider including an ISO Year column in your Dates table (as you already have a Calendar Year) to enable you to include or separate weeks that cross over into a different year as needed...

ISO-8601 calendar example

hansreivers · ‎08-29-2024

Hi @m_dekorte ,

Thanks for your reply. I have the code below. It doesn't matter for me / company that there are less then or more then 7 days in a week.

We want to follow;

the real date year
the "real" ISO week (from 2 untill 51-52), week starts on monday
everything what is "left", should fall into week 1 of 52-53.

In 2022 we have 6 days in week 52 and 8 days in week 1 2023, because of the first of January for us is due to the year 2023 and thus part of week 1...

Therefor I was wondering if I could make a index column from Year(from date) + ISO week HRE...

let
Source = List.Dates(StartDate, Length, #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}}),
StartDate = #date(#"Start year calendar",1, 1),
EndDate = #date(#"End year calendar", 12, 31) ,
Today = DateTime.Date(DateTime.LocalNow()),
Length = Duration.Days(EndDate - StartDate) + 1,
DayOfWeek = Date.DayOfWeek(Today)+1,
StartWeekDate = Date.AddDays(Today, 1-DayOfWeek),
DayOfMonth = Date.Day(Today),
StartMonthDate = Date.AddDays(Today,1-DayOfMonth),
EndQuarterDate = Date.EndOfQuarter(Today),
Custom1 = #"Changed Type",
#"Inserted Year" = Table.AddColumn(Custom1, "Year", each Date.Year([Date])),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([Date])),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date])),
#"Inserted Day" = Table.AddColumn(#"Inserted Quarter", "Day", each Date.Day([Date])),
#"Inserted Quarter Name" = Table.AddColumn(#"Inserted Day", "Quarter Name", each Text.Combine({"Q",Number.ToText([Quarter])})),
#"Inserted YYYYQ" = Table.AddColumn(#"Inserted Quarter Name", "YYYYQ", each [Year] * 10 + [Quarter]),
#"Inserted Quarter End Date" = Table.AddColumn(#"Inserted YYYYQ", "Quarter Date End", each Date.EndOfQuarter([Date])),
#"Inserted Quarter Start Date" = Table.AddColumn(#"Inserted Quarter End Date", "Quarter Date Start", each Date.AddDays(Date.EndOfMonth(Date.AddQuarters([Quarter Date End],-1)),1)),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Quarter Start Date", "Month Name", each Date.MonthName([Date], "en-US"), type text),
#"Inserted Month Name NL" = Table.AddColumn(#"Inserted Month Name", "Month Name NL", each Date.MonthName([Date], "nl-NL")),
#"Inserted DateKey" = Table.AddColumn(#"Inserted Month Name NL", "DateKey", each [Year] * 10000 + [Month] * 100 + [Day]),
#"Reordered Columns" = Table.ReorderColumns(#"Inserted DateKey",{"DateKey", "Date", "Year", "Quarter", "Month", "Quarter Name", "YYYYQ", "Quarter Date Start", "Quarter Date End", "Day", "Month Name"}),
#"Inserted Month Name Short" = Table.AddColumn(#"Reordered Columns", "Month Name Short", each Text.Start([Month Name], 3), type text),
#"Inserted Month Name Short NL" = Table.AddColumn(#"Inserted Month Name Short", "Month Name Short NL", each Date.ToText([Date], "MMM")),
#"Inserted YYYYMM" = Table.AddColumn(#"Inserted Month Name Short NL", "YYYYMM", each [Year]*100 + [#"Month"]),
#"Inserted MMM-YYYY" = Table.AddColumn(#"Inserted YYYYMM", "MMM-YYYY", each Text.Combine({[Month Name Short], "-", Number.ToText([Year])})),
#"Inserted MMM-YYYY NL" = Table.AddColumn(#"Inserted MMM-YYYY", "MMM-YYYY NL", each Text.Combine({[Month Name Short NL], "-", Number.ToText([Year])})),
#"Inserted Month Start Date" = Table.AddColumn(#"Inserted MMM-YYYY NL", "Month Date Start", each Date.AddDays([Date], 1-[Day])),
#"Inserted Month End Date" = Table.AddColumn(#"Inserted Month Start Date", "Month Date End", each Date.EndOfMonth([Month Date Start])),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Month End Date", "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date], "en-US"), type text),
#"Inserted Day Name NL" = Table.AddColumn(#"Inserted Day Name", "Day Name NL", each Date.DayOfWeekName([Date], "nl-NL")),
#"Inserted Day Name Short" = Table.AddColumn(#"Inserted Day Name NL", "Day Name Short", each Text.Start([Day Name], 3), type text),
#"Inserted Day Name Short NL" = Table.AddColumn(#"Inserted Day Name Short", "Day Name Short NL", each Text.Start([Day Name NL], 2)),
#"Inserted ISO Date" = Table.AddColumn(#"Inserted Day Name Short NL", "getISODate", each getISO8601Week([Date])),
#"Inserted ISO Year" = Table.AddColumn(#"Inserted ISO Date", "ISO Year", each Text.Start([getISODate], 4)),
#"Inserted ISO Week" = Table.AddColumn(#"Inserted ISO Year", "ISO Week", each Text.Middle([getISODate], 6, 2)),
#"Inserted ISO Week Name" = Table.AddColumn(#"Inserted ISO Week", "ISO Week Name", each Text.Middle([getISODate], 5, 3)),
#"Changed Type Numeric" = Table.TransformColumnTypes(#"Inserted ISO Week Name",{{"Year", Int64.Type}, {"DateKey", Int64.Type}, {"Quarter", Int64.Type}, {"Month", Int64.Type}, {"YYYYQ", Int64.Type}, {"Day", Int64.Type}, {"YYYYMM", Int64.Type}, {"Day of Week", Int64.Type}, {"ISO Year", Int64.Type}, {"ISO Week", Int64.Type}}),
#"Inserted YYYWW" = Table.AddColumn(#"Changed Type Numeric", "YYYYWW", each [ISO Year] * 100 + [ISO Week]),
#"Inserted Week Start Date" = Table.AddColumn(#"Inserted YYYWW", "Week Date Start", each Date.AddDays([Date], 1-[Day of Week])),
#"Inserted Week End Date" = Table.AddColumn(#"Inserted Week Start Date", "Week Date End", each Date.AddDays([Week Date Start], 6)),
#"Inserted Day Index" = Table.AddColumn(#"Inserted Week End Date", "Day Index", each Duration.Days(Duration.From([Date] - Today))),
#"Inserted Week Index" = Table.AddColumn(#"Inserted Day Index", "Week Index", each Duration.Days(Duration.From(StartWeekDate - [Week Date Start])) / -7),
#"Inserted Month Index" = Table.AddColumn(#"Inserted Week Index", "Month Index", each Number.Round(Duration.Days(Duration.From(StartMonthDate - [Month Date Start])) / -30.4 , 0)),
#"Inserted Quarter Index" = Table.AddColumn(#"Inserted Month Index", "Quarter Index", each Number.Round(Duration.Days(Duration.From(EndQuarterDate - [Quarter Date End])) / -91.25 , 0)),
#"Inserted Year Index" = Table.AddColumn(#"Inserted Quarter Index", "Year Index", each [Year] - Date.Year(Today)),
#"Changed Type Date" = Table.TransformColumnTypes(#"Inserted Year Index",{{"YYYYWW", Int64.Type}, {"Week Index", Int64.Type}, {"Month Index", Int64.Type}, {"Year Index", Int64.Type}, {"Week Date Start", type date}, {"Week Date End", type date}, {"Month Date End", type date}, {"Month Date Start", type date}, {"getISODate", type text}, {"ISO Week Name", type text}, {"Quarter Date Start", type date}, {"Quarter Date End", type date}, {"Quarter Index", Int64.Type}, {"Day Index", Int64.Type}, {"Day Name Short NL", type text}, {"Day Name NL", type text}, {"MMM-YYYY", type text}, {"MMM-YYYY NL", type text}, {"Month Name NL", type text}}),
#"Inserted Current Week" = Table.AddColumn(#"Changed Type Date", "Current Week", each if[Week Index] = 0 then "Current Week" else Text.Combine({[ISO Week Name], "-", Number.ToText([Year])})),
#"Inserted Current Month" = Table.AddColumn(#"Inserted Current Week", "Current Month", each if [Month Index] = 0 then "Current Month" else [#"MMM-YYYY"]),
#"Inserted Current Quarter" = Table.AddColumn(#"Inserted Current Month", "Current Quarter", each if [Quarter Index] = 0 then "Current Quarter" else Text.Combine({[Quarter Name], "-", Number.ToText([Year])})),
#"Inserted Current Year" = Table.AddColumn(#"Inserted Current Quarter", "Current Year", each if [Year Index] = 0 then "Current Year" else Number.ToText([Year])),
#"Inserted Day Type" = Table.AddColumn(#"Inserted Current Year", "Day Type", each if [Day of Week] <= 5 then "Weekdag" else "Weekend"),
#"Rijen gefilterd" = Table.SelectRows(#"Inserted Day Type", each true),
#"Insert YYYY-MM kolom" = Table.AddColumn(#"Rijen gefilterd", "YYYY-MM", each Text.Combine({Text.Start(Text.From([YYYYMM], "nl-NL"), 4), "-", Text.Middle(Text.From([YYYYMM], "nl-NL"), 4)}), type text),
#"Renamed Columns1" = Table.RenameColumns(#"Insert YYYY-MM kolom",{{"YYYYWW", "ISO YYYYWW"}}),
#"Added Custom" = Table.AddColumn(#"Renamed Columns1", "Hulpkolom (eerst in DAX, daarna ""vertaald naar M"" in PQ", each if [Year]=[ISO Year] then [ISO Week] else
if [Year]<=[ISO Year] and [ISO Week]=1 then [ISO Week]+52 else
if [Year]>=[ISO Year] and [ISO Week]=52 then [ISO Week]-51
else
if [Year]>=[ISO Year] and [ISO Week]=53 then [ISO Week]-52
else "KLOPT NIET"),
#"Added Custom Column1" = Table.AddColumn(#"Added Custom", "Jaar(datum) + HRE ISO week", each Text.Combine({Text.PadEnd(Text.From([Year], "nl-NL"), 5, "-"), Text.PadStart(Text.From([#"Hulpkolom (eerst in DAX, daarna ""vertaald naar M"" in PQ"], "nl-NL"), 2, "0")}), type text),
#"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column1",{{"Hulpkolom (eerst in DAX, daarna ""vertaald naar M"" in PQ", Int64.Type}}),
#"Filtered Rows" = Table.SelectRows(#"Changed Type1", each true),
#"Renamed Columns2" = Table.RenameColumns(#"Filtered Rows",{{"Day", "Day Of Month"}}),
#"Inserted Day of Year" = Table.AddColumn(#"Renamed Columns2", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
#"Reordered Columns1" = Table.ReorderColumns(#"Inserted Day of Year",{"DateKey", "Date", "Year", "Quarter", "Month", "Quarter Name", "YYYYQ", "Quarter Date Start", "Quarter Date End", "Day Of Month", "Month Name NL", "Month Name", "Month Name Short", "Month Name Short NL", "YYYYMM", "MMM-YYYY", "MMM-YYYY NL", "Month Date Start", "Month Date End", "Day of Week", "Day Name", "Day Name NL", "Day Name Short", "Day Name Short NL", "getISODate", "ISO Year", "ISO Week", "ISO Week Name", "ISO YYYYWW", "Week Date Start", "Week Date End", "Day Index", "Month Index", "Quarter Index", "Year Index", "Current Week", "Current Month", "Current Quarter", "Current Year", "Day Type", "YYYY-MM", "Hulpkolom (eerst in DAX, daarna ""vertaald naar M"" in PQ", "Week Index", "Jaar(datum) + HRE ISO week", "Day of Year"}),
#"Renamed Columns3" = Table.RenameColumns(#"Reordered Columns1",{{"Week Index", "Week Index F"}}),
#"Inserted Text After Delimiter" = Table.AddColumn(#"Renamed Columns3", "HRE ISO - WEEK", each Text.AfterDelimiter([#"Jaar(datum) + HRE ISO week"], "-"), type text),
#"Reordered Columns2" = Table.ReorderColumns(#"Inserted Text After Delimiter",{"DateKey", "Date", "Year", "Quarter", "Month", "Quarter Name", "YYYYQ", "Quarter Date Start", "Quarter Date End", "Day Of Month", "Month Name NL", "Month Name", "Month Name Short", "Month Name Short NL", "YYYYMM", "MMM-YYYY", "MMM-YYYY NL", "Month Date Start", "Month Date End", "Day of Week", "Day Name", "Day Name NL", "Day Name Short", "Day Name Short NL", "getISODate", "ISO Year", "ISO Week", "ISO Week Name", "ISO YYYYWW", "Week Date Start", "Week Date End", "Day Index", "Month Index", "Quarter Index", "Year Index", "Current Week", "Current Month", "Current Quarter", "Current Year", "Day Type", "YYYY-MM", "Hulpkolom (eerst in DAX, daarna ""vertaald naar M"" in PQ", "Week Index F", "Day of Year", "Jaar(datum) + HRE ISO week", "HRE ISO - WEEK"})
in
#"Reordered Columns2"

m_dekorte · ‎08-29-2024

Hi @hansreivers,

Okay well in that case see if the Custom ISO Week, column does the trick for you.

let
    Source = List.Dates(StartDate, Length, #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}}),
    StartDate = #date(#"Start year calendar",1, 1),
    EndDate = #date(#"End year calendar", 12, 31) ,
    Today = DateTime.Date(DateTime.LocalNow()),
    Length = Duration.Days(EndDate - StartDate) + 1,
    DayOfWeek = Date.DayOfWeek(Today)+1,
    StartWeekDate = Date.AddDays(Today, 1-DayOfWeek),
    DayOfMonth = Date.Day(Today),
    StartMonthDate = Date.AddDays(Today,1-DayOfMonth),
    EndQuarterDate = Date.EndOfQuarter(Today),
    
    #"Inserted Year" = Table.AddColumn(#"Changed Type" , "Year", each Date.Year([Date])),
    weekStart = Day.Monday,
    InsertWeekNumber = Table.AddColumn(#"Inserted Year", "Custom ISO Week", each
        if Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], weekStart)+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), weekStart)+1)+10)/7)
        else if (Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], weekStart)+1)+10)/7)=53 and (Date.DayOfWeek(#date(Date.Year([Date]),12,31), weekStart)+1<4))
        then 1 else Number.RoundDown((Date.DayOfYear([Date])-(Date.DayOfWeek([Date], weekStart)+1)+10)/7), type number),
    InsertCurrWeekOffset = Table.AddColumn(InsertWeekNumber, "CurrWeekOffset", each (Number.From(Date.StartOfWeek([Date], weekStart))-Number.From(Date.StartOfWeek(Today, weekStart)))/7),
    GroupRows = Table.Group(InsertCurrWeekOffset, {"Year"}, 
        {
            {"data", each Table.RemoveLastN( Table.RemoveFirstN( _, 7), 7), type table [Date=nullable date, Year=number, Custom ISO Week=number, CurrWeekOffset=number]}, 
            {"top", each let t = Table.FirstN(_, 7), v = List.Max(List.RemoveMatchingItems(t[Custom ISO Week], {2})) in Table.ReplaceValue( t, v, 1, Replacer.ReplaceValue, {"Custom ISO Week"}), type table [Date=nullable date, Year=number, Custom ISO Week=number, CurrWeekOffset=number]}, 
            {"bottom", each let t = Table.LastN(_, 7), v = List.Max(List.RemoveMatchingItems(t[Custom ISO Week], {53})) in Table.ReplaceValue( t, each [Custom ISO Week], each if List.Contains( {1, 53}, [Custom ISO Week]) then v else [Custom ISO Week], Replacer.ReplaceValue, {"Custom ISO Week"}), type table [Date=nullable date, Year=number, Custom ISO Week=number, CurrWeekOffset=number]}
        }
    ),
    Transform = Table.TransformColumns( GroupRows, {{"top", each let v = List.Mode([Custom ISO Week]), m = List.Mode([CurrWeekOffset]) in Table.ReplaceValue(_, (x)=> x[CurrWeekOffset], (x)=> if x[Custom ISO Week] = v then m else x[CurrWeekOffset], Replacer.ReplaceValue, {"CurrWeekOffset"} ), type table [Date=nullable date, Year=number, Custom ISO Week=number, CurrWeekOffset=number] }, {"bottom", each let v = List.Mode([Custom ISO Week]), m = List.Mode([CurrWeekOffset]) in Table.ReplaceValue(_, (x)=> x[CurrWeekOffset], (x)=> if x[Custom ISO Week] = v then m else x[CurrWeekOffset], Replacer.ReplaceValue, {"CurrWeekOffset"} ), type table [Date=nullable date, Year=number, Custom ISO Week=number, CurrWeekOffset=number] }}),
    UpdatedWeeks = Table.Combine( Table.AddColumn(Transform, "temp", each [top] & [data] & [bottom], type table [Date=nullable date, Year=number, Custom ISO Week=number, CurrWeekOffset=number])[temp] ),
    #"Inserted Month" = Table.AddColumn(UpdatedWeeks, "Month", each Date.Month([Date])),
    #"Inserted Quarter" = Table.AddColumn(#"Inserted Month", "Quarter", each Date.QuarterOfYear([Date])),
    #"Inserted Day" = Table.AddColumn(#"Inserted Quarter", "Day", each Date.Day([Date])),
    #"Inserted Quarter Name" = Table.AddColumn(#"Inserted Day", "Quarter Name", each Text.Combine({"Q",Number.ToText([Quarter])})),
    #"Inserted YYYYQ" = Table.AddColumn(#"Inserted Quarter Name", "YYYYQ", each [Year] * 10 + [Quarter]),
    #"Inserted Quarter End Date" = Table.AddColumn(#"Inserted YYYYQ", "Quarter Date End", each Date.EndOfQuarter([Date])),
    #"Inserted Quarter Start Date" = Table.AddColumn(#"Inserted Quarter End Date", "Quarter Date Start", each Date.AddDays(Date.EndOfMonth(Date.AddQuarters([Quarter Date End],-1)),1)),
    #"Inserted Month Name" = Table.AddColumn(#"Inserted Quarter Start Date", "Month Name", each Date.MonthName([Date], "en-US"), type text),
    #"Inserted Month Name NL" = Table.AddColumn(#"Inserted Month Name", "Month Name NL", each Date.MonthName([Date], "nl-NL")),
    #"Inserted DateKey" = Table.AddColumn(#"Inserted Month Name NL", "DateKey", each [Year] * 10000 + [Month] * 100 + [Day]),
    #"Inserted Month Name Short" = Table.AddColumn(#"Inserted DateKey", "Month Name Short", each Text.Start([Month Name], 3), type text),
    #"Inserted Month Name Short NL" = Table.AddColumn(#"Inserted Month Name Short", "Month Name Short NL", each Date.ToText([Date], "MMM")),
    #"Inserted YYYYMM" = Table.AddColumn(#"Inserted Month Name Short NL", "YYYYMM", each [Year]*100 + [#"Month"]),
    #"Inserted MMM-YYYY" = Table.AddColumn(#"Inserted YYYYMM", "MMM-YYYY", each Text.Combine({[Month Name Short], "-", Number.ToText([Year])})),
    #"Inserted MMM-YYYY NL" = Table.AddColumn(#"Inserted MMM-YYYY", "MMM-YYYY NL", each Text.Combine({[Month Name Short NL], "-", Number.ToText([Year])})),
    #"Inserted Month Start Date" = Table.AddColumn(#"Inserted MMM-YYYY NL", "Month Date Start", each Date.AddDays([Date], 1-[Day])),
    #"Inserted Month End Date" = Table.AddColumn(#"Inserted Month Start Date", "Month Date End", each Date.EndOfMonth([Month Date Start])),
    #"Inserted Day of Week" = Table.AddColumn(#"Inserted Month End Date", "Day of Week", each Date.DayOfWeek([Date])+1, Int64.Type),
    #"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Week", "Day Name", each Date.DayOfWeekName([Date], "en-US"), type text),
    #"Inserted Day Name NL" = Table.AddColumn(#"Inserted Day Name", "Day Name NL", each Date.DayOfWeekName([Date], "nl-NL")),
    #"Inserted Day Name Short" = Table.AddColumn(#"Inserted Day Name NL", "Day Name Short", each Text.Start([Day Name], 3), type text),
    #"Inserted Day Name Short NL" = Table.AddColumn(#"Inserted Day Name Short", "Day Name Short NL", each Text.Start([Day Name NL], 2)),
    #"Inserted ISO Date" = Table.AddColumn(#"Inserted Day Name Short NL", "getISODate", each getISO8601Week([Date])),
    #"Inserted ISO Year" = Table.AddColumn(#"Inserted ISO Date", "ISO Year", each Text.Start([getISODate], 4)),
    #"Inserted ISO Week" = Table.AddColumn(#"Inserted ISO Year", "ISO Week", each Text.Middle([getISODate], 6, 2)),
    #"Inserted ISO Week Name" = Table.AddColumn(#"Inserted ISO Week", "ISO Week Name", each Text.Middle([getISODate], 5, 3)),
    #"Inserted YYYWW" = Table.AddColumn(#"Inserted ISO Week Name", "YYYYWW", each Number.From([ISO Year]) * 100 + Number.From([ISO Week])),
    InsertCustomYYYWW = Table.AddColumn(#"Inserted YYYWW", "MDK YYYYWW", each [Year] * 100 + [Custom ISO Week], Int64.Type),
    #"Inserted Week Start Date" = Table.AddColumn(InsertCustomYYYWW, "Week Date Start", each Date.AddDays([Date], 1-[Day of Week])),
    #"Inserted Week End Date" = Table.AddColumn(#"Inserted Week Start Date", "Week Date End", each Date.AddDays([Week Date Start], 6)),
    #"Inserted Day Index" = Table.AddColumn(#"Inserted Week End Date", "Day Index", each Duration.Days(Duration.From([Date] - Today))),
    #"Inserted Week Index" = Table.AddColumn(#"Inserted Day Index", "Week Index", each Duration.Days(Duration.From(StartWeekDate - [Week Date Start])) / -7),
    #"Inserted Month Index" = Table.AddColumn(#"Inserted Week Index", "Month Index", each Number.Round(Duration.Days(Duration.From(StartMonthDate - [Month Date Start])) / -30.4 , 0)),
    #"Inserted Quarter Index" = Table.AddColumn(#"Inserted Month Index", "Quarter Index", each Number.Round(Duration.Days(Duration.From(EndQuarterDate - [Quarter Date End])) / -91.25 , 0)),
    #"Inserted Year Index" = Table.AddColumn(#"Inserted Quarter Index", "Year Index", each [Year] - Date.Year(Today)),
    #"Inserted Current Week" = Table.AddColumn(#"Inserted Year Index", "Current Week", each if[Week Index] = 0 then "Current Week" else Text.Combine({[ISO Week Name], "-", Number.ToText([Year])})),
    #"Inserted Current Month" = Table.AddColumn(#"Inserted Current Week", "Current Month", each if [Month Index] = 0 then "Current Month" else [#"MMM-YYYY"]),
    #"Inserted Current Quarter" = Table.AddColumn(#"Inserted Current Month", "Current Quarter", each if [Quarter Index] = 0 then "Current Quarter" else Text.Combine({[Quarter Name], "-", Number.ToText([Year])})),
    #"Inserted Current Year" = Table.AddColumn(#"Inserted Current Quarter", "Current Year", each if [Year Index] = 0 then "Current Year" else Number.ToText([Year])),
    #"Inserted Day Type" = Table.AddColumn(#"Inserted Current Year", "Day Type", each if [Day of Week] <= 5 then "Weekdag" else "Weekend"),
    #"Insert YYYY-MM kolom" = Table.AddColumn(#"Inserted Day Type", "ISO YYYYWW", each Text.Combine({Text.Start(Text.From([YYYYMM], "nl-NL"), 4), "-", Text.Middle(Text.From([YYYYMM], "nl-NL"), 4)}), type text),
    ChType = Table.TransformColumnTypes(#"Insert YYYY-MM kolom",{{"Year", Int64.Type}, {"Custom ISO Week", Int64.Type}, {"CurrWeekOffset", Int64.Type}, {"Month", Int64.Type}, {"Quarter", Int64.Type}, {"Day", Int64.Type}, {"YYYYQ", Int64.Type}, {"DateKey", Int64.Type}, {"YYYYMM", Int64.Type}, {"Day of Week", Int64.Type}, {"ISO Year", Int64.Type}, {"ISO Week", Int64.Type}, {"YYYYWW", Int64.Type}, {"MDK YYYYWW", Int64.Type}, {"Day Index", Int64.Type}, {"Week Index", Int64.Type}, {"Month Index", Int64.Type}, {"Quarter Index", Int64.Type}, {"Year Index", Int64.Type}, {"Quarter Name", type text}, {"Month Name", type text}, {"Month Name NL", type text}, {"Month Name Short", type text}, {"Month Name Short NL", type text}, {"MMM-YYYY", type text}, {"MMM-YYYY NL", type text}, {"Day Name", type text}, {"Day Name NL", type text}, {"Day Name Short", type text}, {"Day Name Short NL", type text}, {"getISODate", type text}, {"ISO Week Name", type text}, {"Current Week", type text}, {"Current Month", type text}, {"Current Quarter", type text}, {"Current Year", type text}, {"Day Type", type text}, {"ISO YYYYWW", type text}, {"Quarter Date End", type date}, {"Quarter Date Start", type date}, {"Month Date Start", type date}, {"Month Date End", type date}, {"Week Date Start", type date}, {"Week Date End", type date}}),
    #"Added Custom" = Table.AddColumn(ChType, "Hulpkolom (eerst in DAX, daarna ""vertaald naar M"" in PQ", each if [Year]=[ISO Year] then [ISO Week] else
        if [Year]<=[ISO Year] and [ISO Week]=1 then [ISO Week]+52 else
        if [Year]>=[ISO Year] and [ISO Week]=52 then [ISO Week]-51
        else
        if [Year]>=[ISO Year] and [ISO Week]=53 then [ISO Week]-52
        else "KLOPT NIET"),
    #"Added Custom Column1" = Table.AddColumn(#"Added Custom", "Jaar(datum) + HRE ISO week", each Text.Combine({Text.PadEnd(Text.From([Year], "nl-NL"), 5, "-"), Text.PadStart(Text.From([#"Hulpkolom (eerst in DAX, daarna ""vertaald naar M"" in PQ"], "nl-NL"), 2, "0")}), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Added Custom Column1",{{"Hulpkolom (eerst in DAX, daarna ""vertaald naar M"" in PQ", Int64.Type}}),
    #"Renamed Columns2" = Table.RenameColumns(#"Changed Type1",{{"Day", "Day Of Month"}}),
    #"Inserted Day of Year" = Table.AddColumn(#"Renamed Columns2", "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
    #"Renamed Columns3" = Table.RenameColumns(#"Inserted Day of Year",{{"Week Index", "Week Index F"}}),
    #"Inserted Text After Delimiter" = Table.AddColumn(#"Renamed Columns3", "HRE ISO - WEEK", each Text.AfterDelimiter([#"Jaar(datum) + HRE ISO week"], "-"), type text)
in
    #"Inserted Text After Delimiter"

I hope this is helpful

hansreivers · ‎09-09-2024

Hi @m_dekorte ,

This is very helpful for sure and thank you for the work you are putting in...! but I now have the same weeknumbers as my HRE ISO-Week column. I need these week numbers (with the actual date year) to create an weekindex (the same as my day / month / year index) but with the use of column HRE ISO-week or your Custom ISO Week. Is that possible?

I also want to create a measure where I calculate this weeks turnover compared to last years (Custum Iso Week) turnover. Can you help me with that?

Beneath is for example the year (from date) and the HRE Iso week (or your Custum ISO week). I need to make a measure which shows me the difference. Week 32 untill 34 is than;

32 = - 11.834

33 = + 6.743

34 = + 9.880

It would be nice when I can show that beneath 2023 in the matrix or in a KPI card. Is it possible to use a time intelligence function for that?

Thanks in advance!

m_dekorte · ‎09-10-2024

Hi @hansreivers,

I've updated the Dates table, it now includes the Custom ISO Week and a CurrWeekOffset column. Validate it to confirm it meets your requirements.

Use your offset columns for Time Intelligence needs.

See DM.

Problems with an week index in Power Query

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - June 2025

Fabric Community Update - June 2025

Join the #PBI10 DataViz contest

Problems with an week index in Power Query

Helpful resources

Join our Fabric User Panel

Power BI Monthly Update - June 2025

Fabric Community Update - June 2025