Hello Community - I have this great date table, courtesy of Melissa de Korte, but I am trying to modify the week start. I've tried messing with various things, but so far nothing has worked, or I get an error.
I thought just changing the WDStartNum to 0 in the beginning of the code would work, but it throws an error if I do.
What I want is for Week 2 to begin on Sunday, January 22. Just like in the calendar above.
Essentially, we want the Week to mirror what Excel does by default, which is Start on Sunday and end on Saturday. So, Saturday January 1 needs to be technically the last day of Week 1 (2022). Again, same as the screenshot below.
Any help is appreciated!
//let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional StartOfWeekDay as number, optional WDStartNum as number ) as table =>
let
StartDate = #date(2017, 1, 1),
EndDate = #date(2024,12,31),
Holidays = null,
WDStartNum = 1,
WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
CurrentDate = Date.From(DateTime.FixedLocalNow()),
DayCount = Duration.Days(Duration.From(EndDate - StartDate))+1,
Source = List.Dates(StartDate,DayCount,#duration(1,0,0,0)),
AddToday = if EndDate < CurrentDate then List.Combine( {Source, {CurrentDate}}) else Source,
TableFromList = Table.FromList(AddToday, 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 number),
InsertYearOffset = Table.AddColumn(InsertYear, "YearOffset", each Date.Year([Date]) - Date.Year(Date.From(CurrentDate)), type number),
InsertCompletedYear = Table.AddColumn(InsertYearOffset, "YearCompleted", each Date.EndOfYear([Date]) < Date.From(Date.EndOfYear(CurrentDate)), type logical),
InsertQuarter = Table.AddColumn(InsertCompletedYear, "QuarterOfYear", each Date.QuarterOfYear([Date]), type number),
InsertCalendarQtr = Table.AddColumn(InsertQuarter, "Quarter & Year", each "Q" & Number.ToText([QuarterOfYear]) & " " & Number.ToText([Year]), type text),
InsertQuarternYear = Table.AddColumn(InsertCalendarQtr, "QuarternYear", each [Year] * 10000 + [QuarterOfYear] * 100, type number),
InsertQuarterOffset = Table.AddColumn(InsertQuarternYear, "QuarterOffset", each ((4 * Date.Year([Date])) + Date.QuarterOfYear([Date])) - ((4 * Date.Year(Date.From(CurrentDate))) + Date.QuarterOfYear(Date.From(CurrentDate))), type number),
InsertCompletedQuarter = Table.AddColumn(InsertQuarterOffset, "QuarterCompleted", each Date.EndOfQuarter([Date]) < Date.From(Date.EndOfQuarter(CurrentDate)), type logical),
InsertMonth = Table.AddColumn(InsertCompletedQuarter, "MonthOfYear", each Date.Month([Date]), type number),
InsertDay = Table.AddColumn(InsertMonth, "DayOfMonth", each Date.Day([Date]), type number),
InsertMonthName = Table.AddColumn(InsertDay, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),
InsertMonthShort = Table.AddColumn( InsertMonthName, "MonthShortName", each try Text.Proper( Text.Start([Month Name], 3 )) otherwise Text.Proper( [Month Name] ), type text),
InsertMonthInitial = Table.AddColumn(InsertMonthShort, "Month Initial", each Text.Proper(Text.Start([Month Name], 1)) & Text.Repeat( Character.FromNumber(8203), [MonthOfYear] ), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthInitial, "Month & Year", each [MonthShortName] & " " & Number.ToText([Year]), type text),
InsertMonthnYear = Table.AddColumn(InsertCalendarMonth , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
InsertMonthOffset = Table.AddColumn(InsertMonthnYear, "MonthOffset", each ((12 * Date.Year([Date])) + Date.Month([Date])) - ((12 * Date.Year(Date.From(CurrentDate))) + Date.Month(Date.From(CurrentDate))), type number),
InsertCompletedMonth = Table.AddColumn(InsertMonthOffset, "MonthCompleted", each Date.EndOfMonth([Date]) < Date.From(Date.EndOfMonth(CurrentDate)), type logical),
InsertMonthEnding = Table.AddColumn(InsertCompletedMonth, "MonthEnding", each Date.EndOfMonth([Date]), type date),
InsertDayInt = Table.AddColumn(InsertMonthEnding, "DateInt", each [Year] * 10000 + [MonthOfYear] * 100 + [DayOfMonth], type number),
InsertDayOfYear = Table.AddColumn(InsertDayInt, "Day of Year", each Date.DayOfYear([Date]), Int64.Type),
InsertDayWeek = Table.AddColumn(InsertDayOfYear, "DayOfWeek", each Date.DayOfWeek([Date]) + WDStart, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Proper( Date.ToText([Date], "dddd" )), type text),
InsertDayInitial = Table.AddColumn(InsertDayName, "Weekday Initial", each Text.Proper(Text.Start([DayOfWeekName], 1)) & Text.Repeat( Character.FromNumber(8203), [DayOfWeek] ), type text),
InsertWeekStart = Table.AddColumn(InsertDayInitial, "WeekStarting", each Date.StartOfWeek( [Date], WDStartNum), type date),
InsertWeekOffset = Table.AddColumn(InsertWeekStart, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], WDStartNum))-Number.From(Date.StartOfWeek(CurrentDate, WDStartNum)))/7, type number),
InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", each Date.EndOfWeek( [Date], WDStartNum) < Date.From(Date.EndOfWeek(CurrentDate, WDStartNum)), type logical),
InsertWeekEnding = Table.AddColumn(InsertCompletedWeek, "WeekEnding", each Date.EndOfWeek( [Date], WDStartNum), type date),
AddFY = Table.AddColumn(InsertWeekEnding, "Fiscal Year", each "FY" & (if [MonthOfYear] >= WDStartNum and WDStartNum >1 then Text.PadEnd( Text.End( Text.From([Year] +1), 2), 2, "0") else Text.End( Text.From([Year]), 2)), type text),
AddFQ = Table.AddColumn(AddFY, "Fiscal Quarter", each "Q" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (WDStartNum -1) )) / 3 )), type text),
AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [MonthOfYear] >= WDStartNum and WDStartNum >1 then [Year] +1 else [Year]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (WDStartNum -1) )) / 3 ) * 100, type number),
AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period", each if [MonthOfYear] >= WDStartNum and WDStartNum >1 then [MonthOfYear] - (WDStartNum-1) else if [MonthOfYear] >= WDStartNum and WDStartNum =1 then [MonthOfYear] else [MonthOfYear] + (12-WDStartNum+1), type text),
AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", each (if [MonthOfYear] >= WDStartNum and WDStartNum >1 then [Year] +1 else [Year]) * 10000 + [Fiscal Period] * 100, type number),
FYCalendarStart = #date( Date.Year(StartDate)-1, WDStartNum, 1 ),
InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [MonthOfYear] >= WDStartNum and WDStartNum >1 then #date( Date.Year([Date])+1, WDStartNum, 1) else #date( Date.Year([Date]), WDStartNum, 1) ),
AddFYDateRange = Table.Buffer( Table.ExpandTableColumn( Table.ExpandTableColumn( Table.AddColumn( Table.Group( Table.Group( Table.AddColumn( Table.AddColumn(
Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( { Number.From(FYCalendarStart)..Number.From(EndDate) }, Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "Date"}}),
"FiscalFirstDay", each if Date.Month([Date]) < WDStartNum then #date( Date.Year([Date]), WDStartNum, 1) else #date( Date.Year([Date])+1, WDStartNum, 1)),
"FWStartDate", each Date.AddYears(Date.StartOfWeek( [Date], WDStartNum), 1)),
{"FiscalFirstDay", "FWStartDate"}, {{"AllRows", each _, type table [Date=nullable date, FiscalFirstDay=date, FWStartDate=date]}}),
{"FiscalFirstDay"}, {{"AllRows2", each _, type table [FiscalFirstDay=date, FWStartDate=date, AllRows=table]}}),
"Custom", each Table.AddIndexColumn( [AllRows2], "FY Week", 1, 1))[[Custom]],
"Custom", {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}, {"FiscalFirstDay", "FWStartDate", "AllRows", "FY Week"}),
"AllRows", {"Date"}, {"Date"})[[Date], [FY Week]]
),
MergeFYW = Table.NestedJoin(InsertFFD, {"Date"}, AddFYDateRange, {"Date"}, "AddFYWeek", JoinKind.LeftOuter),
ExpandFYWeek = Table.TransformColumnTypes( Table.ExpandTableColumn(MergeFYW, "AddFYWeek", {"FY Week"}, {"Fiscal Week"}),{{"Fiscal Week", Int64.Type}}),
AddFYW = Table.AddColumn( ExpandFYWeek, "Fiscal Year & Week", each if [MonthOfYear] >= WDStartNum and WDStartNum >1 then Text.PadStart(Text.From([Fiscal Week]), 2, "0") & "-" & Text.From( Date.Year([Date])+1) else Text.PadStart( Text.From([Fiscal Week]), 2, "0") & "-" & Text.From( Date.Year([Date])) , type text),
InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each (if [MonthOfYear] >= WDStartNum and WDStartNum >1 then Date.Year([Date])+1 else Date.Year([Date])) * 10000 + [Fiscal Week] * 100, Int64.Type),
InsertIsAfterToday = Table.AddColumn(InsertFWeeknYear, "IsAfterToday", each not ([Date] <= Date.From(CurrentDate)), type logical),
InsertIsWorkingDay = Table.AddColumn(InsertIsAfterToday, "IsWorkingDay", each if Date.DayOfWeek([Date], Day.Monday) > 4 then false else true, type logical),
InsertIsHoliday = Table.AddColumn(InsertIsWorkingDay, "IsHoliday", each if Holidays = null then "Unknown" else List.Contains( Holidays, [Date] ), if Holidays = null then type text else type logical),
InsertIsBusinessDay = Table.AddColumn(InsertIsHoliday, "IsBusinessDay", each if [IsWorkingDay] = true and [IsHoliday] <> true then true else false, type logical),
InsertDayType = Table.AddColumn(InsertIsBusinessDay, "Day Type", each if [IsHoliday] = true then "Holiday" else if [IsWorkingDay] = false then "Weekend" else if [IsWorkingDay] = true then "Weekday" else null, type text),
CurrentDateRecord = Table.SelectRows(InsertDayType, each ([Date] = CurrentDate)),
CurrentISOyear = CurrentDateRecord{0}[ISO Year],
CurrentISOqtr = CurrentDateRecord{0}[ISO QuarterOfYear],
CurrentYear = CurrentDateRecord{0}[Year],
CurrentMonth = CurrentDateRecord{0}[MonthOfYear],
CurrentFiscalFirstDay = CurrentDateRecord{0}[FiscalFirstDay],
PrevFiscalFirstDay = Date.AddYears(CurrentFiscalFirstDay, -1),
CurrentFQ = CurrentDateRecord{0}[FQuarternYear],
CurrentFP = CurrentDateRecord{0}[FPeriodnYear],
CurrentFW = CurrentDateRecord{0}[FWeeknYear],
InsertFYoffset = Table.AddColumn(InsertDayType, "FiscalYearOffset", each try (if [MonthOfYear] >= WDStartNum then [Year]+1 else [Year]) - (if CurrentMonth >= WDStartNum then CurrentYear+1 else CurrentYear) otherwise null, type number),
InsertCurrentFQ = Table.AddColumn(InsertFYoffset, "IsCurrentFQ", each if [FQuarternYear] = CurrentFQ then true else false, type logical),
InsertCurrentFP = Table.AddColumn(InsertCurrentFQ, "IsCurrentFP", each if [FPeriodnYear] = CurrentFP then true else false, type logical),
InsertCurrentFW = Table.AddColumn(InsertCurrentFP, "IsCurrentFW", each if [FWeeknYear] = CurrentFW then true else false, type logical),
InsertPYTD = Table.AddColumn(InsertCurrentFW, "IsPYTD", each if CurrentYear-1 = [Year] and [Day of Year] <= CurrentDateRecord{0}[Day of Year] then true else false, type logical),
ListPrevFYDates = List.Buffer( Table.SelectRows( Table.ExpandTableColumn( Table.NestedJoin(
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( PrevFiscalFirstDay, Number.From(CurrentFiscalFirstDay-PrevFiscalFirstDay),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1), {"Index"},
Table.AddIndexColumn( Table.RenameColumns( Table.TransformColumnTypes( Table.FromList( List.Dates( Date.AddYears( PrevFiscalFirstDay, -1), Number.From( PrevFiscalFirstDay - Date.AddYears( PrevFiscalFirstDay, -1)),#duration(1,0,0,0)), Splitter.SplitByNothing()),{{"Column1", type date}}), {{"Column1", "DateFY"}}), "Index", 1, 1)
, {"Index"}, "Table", JoinKind.LeftOuter), "Table", {"DateFY"}, {"PrevDateFY"}), each [DateFY] <= CurrentDate)[PrevDateFY] ),
InsertPFYTD = Table.AddColumn(InsertPYTD, "IsPFYTD", each if [FiscalYearOffset] = -1 and List.Contains(ListPrevFYDates, [Date] ) then true else false, type logical),
RemoveToday = Table.RemoveColumns( if EndDate < CurrentDate then Table.SelectRows(InsertPFYTD, each ([Date] <> CurrentDate)) else InsertPFYTD, {"Day of Year", "FiscalFirstDay"}),
#"Renamed Columns" = Table.RenameColumns(RemoveToday,{{"Fiscal Week", "Week Number"}, {"Fiscal Year & Week", "Week & Year"}, {"QuarterOfYear", "Quarter Number"}, {"MonthOfYear", "Month Number"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"Year", Int64.Type}, {"DateInt", Int64.Type}, {"WeekOffset", Int64.Type}, {"FPeriodnYear", Int64.Type}, {"YearOffset", Int64.Type}, {"Fiscal Period", Int64.Type}, {"FQuarternYear", Int64.Type}, {"MonthOffset", Int64.Type}, {"MonthnYear", Int64.Type}, {"Month Number", Int64.Type}, {"DayOfMonth", Int64.Type}, {"QuarternYear", Int64.Type}, {"QuarterOffset", Int64.Type}, {"Quarter Number", Int64.Type}}),
#"Renamed Columns1" = Table.RenameColumns(#"Changed Type",{{"Fiscal Quarter", "Quarter"}}),
InsertWeekNumber = Table.AddColumn(#"Renamed Columns1", "Custom", each "Week " & Text.End("0" & Text.From([Week Number]),2)),
#"Renamed Columns2" = Table.RenameColumns(InsertWeekNumber,{{"Custom", "Week Num"}}),
#"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns2",{{"Week Num", type text}}),
#"Reordered Columns" = Table.ReorderColumns(#"Changed Type1",{"Date", "Year", "YearOffset", "YearCompleted", "Quarter Number", "Quarter & Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "Month Number", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month & Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "WeekStarting", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year", "Quarter", "FQuarternYear", "Fiscal Period", "FPeriodnYear", "Week Number", "Week & Year", "Week Num", "FWeeknYear", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "Day Type", "FiscalYearOffset", "IsCurrentFQ", "IsCurrentFP", "IsCurrentFW", "IsPYTD", "IsPFYTD"})
in
#"Reordered Columns" // documentation = [
//Documentation.Name = " fxCalendar",
//Documentation.Description = " Date table function to create a calendar",
//Documentation.LongDescription = " Date table function to create a calendar",
//Documentation.Category = " Table",
//Documentation.Version = " 1.00: Added optinal StartofWeekDay parameter and Removed all ISO-8601 columns",
//Documentation.Source = " local",
//Documentation.Author = " Melissa de Korte",
//Documentation.Examples = { [Description = " See: https://forum.enterprisedna.co/t/extended-date-table-power-query-m-function/6390/145",
// Code = " Optional paramters: #(lf)
// (FYStartMonthNum) Month number the fiscal year starts, Januari if omitted #(lf)
// (Holidays) Select a query (and column) that contains a list of holiday dates #(lf)
// (StartOfWeekDay) Set start of week day by entering a value between 0-6, where 0 = sunday. If omitted weeks start on monday. #(lf)
// (WDStartNum) Switch default weekday numbering from 0-6 to 1-7 by entering a 1 #(lf)
// #(lf)
// Important to note: #(lf)
// [Fiscal Week] starts on a the selected weekday and can contain less than 7 days in a First- and/or Last Week of a FY #(lf)
// [IsWorkingDay] does not take holiday dates into account #(lf)
// [IsBusinessDay] does take optional holiday dates into account #(lf)
// [IsPYTD] and [IsPFYTD] compare Previous [Day of Year] with the Current [Day of Year] number, so dates don't align in leap years",
// Result = " " ] }
//]
//in
//Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), documentation))
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
