Extended Date Table (Power Query M function)

[BrianJ]

All,

Here is the full C# script from @AntrikshSharma and @Melissa for automatically sorting the Extended Date Table in Tabular Editor/TE3:

---

// IMPORTANT! requires "unsupported modelling operations" to be enabled. Not supported by MSFT, use at your own risk. 
// Not recommended for use in production models.
var DatesTable = "Dates"; // Change the name between double quotes to your 'default' date table name
var colOperations = new List<Tuple<string, string, bool>>{
            Tuple.Create("Quarter & Year", "QuarternYear", true),
            Tuple.Create("Month Name", "MonthOfYear", true),
            Tuple.Create("MonthShortName", "MonthOfYear", true),
            Tuple.Create("Month Initial", "MonthOfYear", true),
            Tuple.Create("Month & Year", "MonthnYear", true),
            Tuple.Create("DayOfWeekName", "DayOfWeek", true),
            Tuple.Create("Weekday Initial", "DayOfWeek", true),
            Tuple.Create("ISO Quarter", "ISO QuarterOfYear", true),
            Tuple.Create("ISO Quarter & Year", "ISO QuarternYear", true),
            Tuple.Create("Week & Year", "WeeknYear", true),
            Tuple.Create("Fiscal Year", "FiscalYearOffset", false),
            Tuple.Create("Fiscal Quarter", "FQuarternYear", false),
            Tuple.Create("Fiscal Year & Week", "FWeeknYear", false)
        };
foreach(var t in Model.Tables){
    if (t.Name == DatesTable){
        {
            t.DataCategory = "Time"; //Mark as Date table
        }
        foreach (var c in t.Columns){
            for (int i = 0; i < colOperations.Count; ++i)
            if (c.Name == colOperations[i].Item1){
                    c.SortByColumn = t.Columns[colOperations[i].Item2]; //Set Sort By
                    t.Columns[colOperations[i].Item2].IsHidden = colOperations[i].Item3; //Set Hidden
                }
            if (c.DataType == DataType.Double || c.DataType == DataType.Int64){
                c.SummarizeBy = AggregateFunction.None; //Set Summarize By
                c.FormatString = "0"; //Set Format numeric fields
            }
            else if (c.DataType == DataType.DateTime){
                c.FormatString = "Short Date"; // Set Format date fields
            }
        }
    }
}

[Fcovey]

Thank you for sharing Melissa’s extended date table code. Unfortunately, every time I refresh all the data set in my model, I receive the following “errors in dates” message. This creates several error tables which I cannot figure out. Has anyone else encountered this issue? What am I doing incorrectly? Thank you in advance.

[Melissa]

Hi @Fcovey,

Welcome to the forum.
Can you share that file? The supplied pictures don’t provide enough for me to go on…

Thanks!

[Fcovey]

Hi Melissa,

Thanks for your quick response. As requested, attached is the PBIX working file.

MonthlyReportingv3.1 - Copy.pbix (7.7 MB)

[Melissa]

Your inquiry referred to the topic below, please see my response here:

Basic Date Table (Power Query M function)

Hi @Fcovey, I noticed you’re using the basic date table… There was a type mismatch which now has been resolved, thanks for reporting it! You can copy an updated version of the basic date table above (from the initial post). For future reference I’ll leave a link to your post below. I hope this is helpful.

[Jonah]

Please how can I customize this Date Table to have my first quarter start from October and not from January.
Thank you.

[Keith]

Hi @Jonah

You might want to read the first message and review the youtube video(also part of the first message) that @Melissa explaining the Extended date table.

FxCalendar

Put in FYstartmonth number enter 10. That will be your first month in the fiscal year

I hope this helps
Keith

[Jonah]

Hey, Keith,
Many thanks for your reply.
I did that as you suggested and it’s great. However, I would have loved to have FYQ1 starting from October because it is still giving me January.
Please kindly assist.

Please how can I get the remainder of the value from a period?
For example, I work in the HIVAIDs space, and targets are set say from October 2021 to September 2022. Say 100 was set as target and so far 45 has been achieved. How can I calculate in DAX to get the remainder so that as time progresses it recalculates based on the remaining timeframe and set targets in either, days, weeks or months.
Many thanks,
Jonah

[Keith]

Hi @Jonah

Sound like you didn’t do it correct or you not looking at the right columns to reflect the fiscal year
I have attached a file for your reference.

If it doesn’t work, please make a new topic

fy20 starts in october within the file…please review it.
Johanedna date table.pbix (88.9 KB)

thanks
Keith

[Jonah]

Hi,
Super Thanks!!!
I think I am using an old date table.

Please any solution to this below?
Lets say I have a set target of HIV test of 1000 from Oct 2021 to September 2022. As at June 2022 we have achieved 700 (70%) remaining 300 (30%) to be achieved in Jul/Aug/Sept (i.e Q4). How can I calculate the following and have it show up on timeline bar on my dashboard.

1. 300 to be recalibrated across the 3 months to show daily (300/no of remaining days),
   weekly(300/no of remaining week) and monthly target (300/no of remaining Month).
2. Display the number of remaining days, week, months to end the FY

[Keith]

start a new topic please as this topic is closed…You will get better results if you open a new topic. People in the forum sometime don’t read solved item.
thanks

[BrianJ]

All,

For an analsysis I was doing, I found it helpful to have a dayoffset column in this table so I added it to the version of @Melissa Extended Date table below:

let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional Holidays as list, optional WDStartNum as number ) as table =>
  let
    FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 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),
    InsertDayOffset = Table.AddColumn(InsertYear, "DayOffset", each Number.From([Date]) - Number.From(CurrentDate)),
    InsertYearOffset = Table.AddColumn(InsertDayOffset, "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], Day.Monday) + 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),
    
    InsertWeekNumber= Table.AddColumn(InsertDayInitial, "ISO Weeknumber", 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), type number),
    InsertISOyear = Table.AddColumn(InsertWeekNumber, "ISO Year", each Date.Year( Date.AddDays( Date.StartOfWeek([Date], Day.Monday), 3 )),  Int64.Type),
    BufferTable = Table.Buffer(Table.Distinct( InsertISOyear[[ISO Year], [DateInt]])),
    InsertISOqNum = Table.AddColumn(InsertISOyear, "ISO QuarterOfYear", each if [ISO Weeknumber] >39 then 4 else if [ISO Weeknumber] >26 then 3 else if [ISO Weeknumber] >13 then 2 else 1, Int64.Type),
    InsertISOqtr = Table.AddColumn(InsertISOqNum, "ISO Quarter", each "Q" & Number.ToText([ISO QuarterOfYear]), type text),
    InsertISOQuarter = Table.AddColumn(InsertISOqtr, "ISO Quarter & Year", each "Q" & Number.ToText([ISO QuarterOfYear]) & " " & Number.ToText([ISO Year]), type text),
    InsertISOqNy = Table.AddColumn(InsertISOQuarter, "ISO QuarternYear", each [ISO Year] * 10000 + [ISO QuarterOfYear] * 100, type number),
    //InsertISOday = Table.AddColumn(InsertISOqNy, "ISO Day of Year", (OT) => Table.RowCount( Table.SelectRows( BufferTable, (IT) => IT[DateInt] <= OT[DateInt] and IT[ISO Year] = OT[ISO Year])),  Int64.Type),
    InsertCalendarWk = Table.AddColumn(InsertISOqNy, "Week & Year", each Text.From([ISO Year]) & "-" & Text.PadStart( Text.From( [ISO Weeknumber] ), 2, "0"), type text ),
    InsertWeeknYear = Table.AddColumn(InsertCalendarWk, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100,  Int64.Type),
    InsertWeekOffset = Table.AddColumn(InsertWeeknYear, "WeekOffset", each (Number.From(Date.StartOfWeek([Date], Day.Monday))-Number.From(Date.StartOfWeek(CurrentDate, Day.Monday)))/7, type number),
    InsertCompletedWeek = Table.AddColumn(InsertWeekOffset, "WeekCompleted", each Date.EndOfWeek( [Date], Day.Monday) < Date.From(Date.EndOfWeek(CurrentDate, Day.Monday)), type logical),
    InsertWeekEnding = Table.AddColumn(InsertCompletedWeek, "WeekEnding", each Date.EndOfWeek( [Date], Day.Monday), type date),

    AddFY = Table.AddColumn(InsertWeekEnding, "Fiscal Year", each "FY" & (if [MonthOfYear] >= FYStartMonth and FYStartMonth >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 "FQ" & Text.From( Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 )), type text),
    AddFQnYr = Table.AddColumn(AddFQ, "FQuarternYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + Number.RoundUp( Date.Month( Date.AddMonths( [Date], - (FYStartMonth -1) )) / 3 ) * 100, type number),
    AddFM = Table.AddColumn(AddFQnYr, "Fiscal Period", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [MonthOfYear] - (FYStartMonth-1) else if [MonthOfYear] >= FYStartMonth and FYStartMonth =1 then [MonthOfYear] else [MonthOfYear] + (12-FYStartMonth+1), type text),
    AddFMnYr = Table.AddColumn(AddFM , "FPeriodnYear", each (if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then [Year] +1 else [Year]) * 10000 + [Fiscal Period] * 100, type number),
    FYCalendarStart = #date( Date.Year(StartDate)-1, FYStartMonth, 1 ),
    InsertFFD = Table.AddColumn( AddFMnYr, "FiscalFirstDay", each if [MonthOfYear] >= FYStartMonth and FYStartMonth >1 then #date( Date.Year([Date])+1, FYStartMonth, 1) else #date( Date.Year([Date]), FYStartMonth, 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]) < FYStartMonth then #date( Date.Year([Date]), FYStartMonth, 1) else #date( Date.Year([Date])+1, FYStartMonth, 1)),
        "FWStartDate", each  Date.AddYears(Date.StartOfWeek( [Date], Day.Monday), 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}}),
    UpdateFYWeek = if FYStartMonthNum =null then
      Table.ReplaceValue(ExpandFYWeek, each [Fiscal Week], each if FYStartMonth =1 then [#"ISO Weeknumber"] else [Fiscal Week],Replacer.ReplaceValue,{"Fiscal Week"})
      else ExpandFYWeek,
    AddFYW = Table.AddColumn( UpdateFYWeek, "Fiscal Year & Week", each if FYStartMonthNum =null then [#"Week & Year"] else if Date.Month([Date]) < FYStartMonth then Text.From( Date.Year([Date])) & "-" & Text.PadStart( Text.From([Fiscal Week]), 2, "0") else Text.From( Date.Year([Date])+1) & "-" & Text.PadStart(Text.From([Fiscal Week]), 2, "0"), type text),
    InsertFWeeknYear = Table.AddColumn(AddFYW, "FWeeknYear", each if FYStartMonthNum =null then [WeeknYear] else (if Date.Month([Date]) < FYStartMonth then Date.Year([Date]) else Date.Year([Date])+1) * 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],
    InsertISOQtrOffset = Table.AddColumn(InsertDayType, "ISO QuarterOffset", each ((4 * [ISO Year]) +  [ISO QuarterOfYear]) - ((4 * CurrentISOyear) + CurrentISOqtr), type number),
    InsertISOYrOffset = Table.AddColumn(InsertISOQtrOffset, "ISO YearOffset", each [ISO Year] - CurrentISOyear, type number),
    InsertFYoffset = Table.AddColumn(InsertISOYrOffset, "FiscalYearOffset", each try (if [MonthOfYear] >= FYStartMonth then [Year]+1 else [Year]) - (if CurrentMonth >= FYStartMonth 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"}),
    ChType = Table.TransformColumnTypes(RemoveToday,{{"Year", Int64.Type}, {"QuarterOfYear", Int64.Type}, {"MonthOfYear", Int64.Type}, {"DayOfMonth", Int64.Type}, {"DateInt", Int64.Type}, {"DayOfWeek", Int64.Type}, {"ISO YearOffset", Int64.Type}, {"ISO QuarternYear", Int64.Type}, {"ISO QuarterOffset", Int64.Type}, {"ISO Weeknumber", Int64.Type}, {"WeeknYear", Int64.Type}, {"MonthnYear", Int64.Type}, {"QuarternYear", Int64.Type}, {"FQuarternYear", Int64.Type}, {"Fiscal Period", Int64.Type}, {"FPeriodnYear", Int64.Type}, {"WeekOffset", Int64.Type}, {"MonthOffset", Int64.Type}, {"DayOffset", Int64.Type}, {"QuarterOffset", Int64.Type}, {"YearOffset", Int64.Type}, {"FiscalYearOffset", Int64.Type}}),
    ReorderColumns = Table.ReorderColumns(ChType, {"Date", "Year", "YearOffset", "YearCompleted", "QuarterOfYear", "Quarter & Year", "QuarternYear", "QuarterOffset", "QuarterCompleted", "MonthOfYear", "DayOfMonth", "Month Name", "MonthShortName", "Month Initial", "Month & Year", "MonthnYear", "MonthOffset", "MonthCompleted", "MonthEnding", "DateInt", "DayOfWeek", "DayOfWeekName", "Weekday Initial", "Day Type", "ISO Year", "ISO YearOffset", "ISO QuarterOfYear", "ISO Quarter", "ISO Quarter & Year", "ISO QuarternYear", "ISO QuarterOffset", "ISO Weeknumber", "Week & Year", "WeeknYear", "WeekOffset", "WeekCompleted", "WeekEnding", "Fiscal Year", "FiscalYearOffset", "Fiscal Quarter", "FQuarternYear", "IsCurrentFQ", "Fiscal Period", "FPeriodnYear", "IsCurrentFP", "Fiscal Week", "Fiscal Year & Week", "FWeeknYear", "IsCurrentFW", "IsAfterToday", "IsWorkingDay", "IsHoliday", "IsBusinessDay", "IsPYTD", "IsPFYTD"}, MissingField.UseNull)
  in
    ReorderColumns,
    Documentation = [
      Documentation.Name =  " fxCalendar", 
      Documentation.Description = " Date table function to create an ISO-8601 calendar", 
      Documentation.LongDescription = " Date table function to create an ISO-8601 calendar", 
      Documentation.Category = " Table", 
      Documentation.Version = " 1.32: Adjusted fiscal weeks logic depending on wheter a fiscal start month was submitted",
      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", 
        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) 
        (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 Monday 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 ))

[Keith]

hi @BrianJ,
@Melissa CC
I was just wondering if there will be an updated version on the Analyst Hub?
Thanks
Keith

[BrianJ]

@Keith ,

Very funny you mention that - I saw your message when I came back here to copy the code to post it on the Analyst Hub.

It’s up there now at:

EDNA Extended Date Table with DayOffset - 20220727 version - Analyst Hub by...

[Keith]

it was just a thought

[Keith]

HI @BrianJ,

Just a question: Should you have hard code date?

StartOfWeekDayName = Text.Proper( Text.Start( Date.DayOfWeekName( #date(2021, 2,1) ), 3))

Thanks
Keith

[BrianJ]

@Keith ,

Wow – awesome catch! I actually had pasted the wrong code in.

I’ve subsequently corrected it in both the post above and in the Analyst Hub version.

Thanks very much!

– Brian

[DavieJoe]

Sharp eyes

[BrianJ]

@DavieJoe ,

@Keith has got his eye on me now…

[Keith]

@BrianJ …never the eye…
i just saw and wasn’t sure
Its all good