fnDateTable - standard ISO 1086 Calendar - Copy as paste in blank query
let
Source = let
fnDateTable = (
StartDate as date,
EndDate as date,
optional FYStartMonthNum as number,
optional Holidays as list,
optional WDStartNum as number
) as table =>
let
DateToday = DateTime.Date ( DateTime.LocalNow()),
CompleteMTD = Date.AddDays( Date.StartOfMonth( DateTime.Date ( DateTime.LocalNow())), -1 ),
ThisMonth = Date.EndOfMonth( DateTime.Date ( DateTime.LocalNow())),
NextMonths1 = Date.EndOfMonth(Date.AddMonths(Date.AddDays(Date.StartOfMonth(DateTime.Date ( DateTime.LocalNow())),-1),+1)),
NextMonths2 = Date.EndOfMonth(Date.AddMonths(Date.AddDays(Date.StartOfMonth(DateTime.Date ( DateTime.LocalNow())),-1),+2)),
NextMonths3 = Date.EndOfMonth(Date.AddMonths(Date.AddDays(Date.StartOfMonth(DateTime.Date ( DateTime.LocalNow())),-1),+3)),
FYStartMonth = if List.Contains({1 .. 12}, FYStartMonthNum) then FYStartMonthNum else 1,
StartOfWeekDayName = Text.Proper(Text.Start(Date.DayOfWeekName(#date(2021, 2, 1)), 3)),
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]) & " " & Text.End(Number.ToText([Year]), 2),
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] & " " & Text.End(Number.ToText([Year]), 2),
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
),
InsertMonthStarting = Table.AddColumn(
InsertCompletedMonth,
"MonthStarting",
each Date.StartOfMonth([Date]),
type date
),
InsertMonthEnding = Table.AddColumn(
InsertMonthStarting,
"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
),
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
),
InsertWeekStarting = Table.AddColumn(
InsertCompletedWeek,
"WeekStarting",
each Date.StartOfWeek([Date], Day.Monday),
type date
),
InsertWeekEnding = Table.AddColumn(
InsertWeekStarting,
"WeekEnding",
each Date.EndOfWeek([Date], Day.Monday),
type date
),
AddFY = Table.AddColumn(
InsertWeekEnding,
"Fiscal Year",
each "FY"
& (
if [MonthOfYear] >= FYStartMonth 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 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 then
[MonthOfYear] - (FYStartMonth - 1)
else
[MonthOfYear] + (12 - FYStartMonth + 1),
type text
),
AddFMnYr = Table.AddColumn(
AddFM,
"FPeriodnYear",
each (if [MonthOfYear] >= FYStartMonth 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 Date.Month([Date]) < FYStartMonth then
#date(Date.Year([Date]), FYStartMonth, 1)
else
#date(Date.Year([Date]) + 1, 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}}
),
AddFYW = Table.AddColumn(
ExpandFYWeek,
"Fiscal Year & Week",
each
if FYStartMonth = 1 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 FYStartMonth = 1 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],
MonthAdd1 = CurrentDateRecord{0}[FPeriodnYear]+100,
MonthAdd2 = CurrentDateRecord{0}[FPeriodnYear]+200,
MonthAdd3 = CurrentDateRecord{0}[FPeriodnYear]+300,
DateAdd1 = Date.EndOfMonth(Date.AddMonths(Date.AddDays(Date.StartOfMonth(DateTime.Date ( DateTime.LocalNow())),-1),+1)),
DateAdd2 = Date.EndOfMonth(Date.AddMonths(Date.AddDays(Date.StartOfMonth(DateTime.Date ( DateTime.LocalNow())),-1),+2)),
DateAdd3 = Date.EndOfMonth(Date.AddMonths(Date.AddDays(Date.StartOfMonth(DateTime.Date ( DateTime.LocalNow())),-1),+3)),
PrevMonthDate = Table.AddColumn(
MonthAdd3,
"PrevMDate",
each Date.EndOfMonth(Date.AddMonths([MonthEnding], - 1)),
type date
),
PrevMonth = Table.AddColumn(
PrevMonthDate,
"PMonthOfYear",
each Date.Month([PrevMDate]),
type number
),
PrevFP = Table.AddColumn(
PrevMonth,
"PrevFP",
each
if [PMonthOfYear] >= FYStartMonth then
[PMonthOfYear] - (FYStartMonth - 1)
else
[PMonthOfYear] + (12 - FYStartMonth + 1),
type text
),
PFPeriodnYear = Table.AddColumn(
PrevFP,
"PFPeriodnYear",
each (if [PMonthOfYear] >= FYStartMonth then [Year] + 1 else [Year])
* 10000 + [PrevFP]
* 100,
type number
),
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
),
InsertCurrentFW = Table.AddColumn(
InsertCurrentFQ,
"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 Weeknumber", Int64.Type},
{"WeeknYear", Int64.Type},
{"MonthnYear", Int64.Type},
{"QuarternYear", Int64.Type},
{"Fiscal Period", Int64.Type},
{"WeekOffset", Int64.Type},
{"MonthOffset", Int64.Type},
{"QuarterOffset", Int64.Type},
{"YearOffset", Int64.Type},
{"FiscalYearOffset", Int64.Type}
}
),
InsertCurrentFP = Table.AddColumn(
ChType,
"IsCurrentFP",
each if [FPeriodnYear] = CurrentFP then true else false,
type logical
),
ReorderColumns = Table.ReorderColumns(
InsertCurrentFP,
{
"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
),
IsForecast1 = Table.AddColumn(
ReorderColumns,
"IsMonthAdd1",
each if [FPeriodnYear] = MonthAdd1 then true else false,
type logical
),
IsForecast2 = Table.AddColumn(
IsForecast1,
"IsMonthAdd2",
each if [FPeriodnYear] = MonthAdd2 then true else false,
type logical
),
IsForecast3 = Table.AddColumn(
IsForecast2,
"IsMonthAdd3",
each if [FPeriodnYear] = MonthAdd3 then true else false,
type logical
),
IsMTD = Table.AddColumn(
IsForecast3,
"IsMTD",
each if [MonthEnding] = cCompleteMTD then true else false,
type logical
),
IsThisMonth = Table.AddColumn(
IsMTD,
"IsThisMonth",
each if [MonthEnding] = ThisMonth then true else false,
type logical
),
IsCompleteMonth = Table.AddColumn(
IsThisMonth,
"IsCompleteMonth",
each if [MonthEnding] <= cToday then true else false,
type logical
),
YTDAdd1 = Table.AddColumn(
IsCompleteMonth,
"YTDAdd1",
each if [MonthEnding] <= DateAdd1 then true else false,
type logical
),
YTDAdd2 = Table.AddColumn(
YTDAdd1,
"YTDAdd2",
each if [MonthEnding] <= DateAdd2 then true else false,
type logical
),
YTDAdd3 = Table.AddColumn(
YTDAdd2,
"YTDAdd3",
each if [MonthEnding] <= DateAdd3 then true else false,
type logical
)
in
YTDAdd3
,
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.25: Added ISO Quarters and Offsets",
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))
in
Source
Comments