Hi,
I am hoping someone can help.
I have created a date table in Power Query using the following code:
let fnDateTable = (StartDate as date, EndDate as date, optional FYStartMonthNum as number, optional WDStartNum as number) as table =>
let
FYStartMonth = if List.Contains( {1..12}, FYStartMonthNum ) then FYStartMonthNum else 1,
CurrentDate = Date.From(DateTime.FixedLocalNow()),
WDStart = if List.Contains( {0, 1}, WDStartNum ) then WDStartNum else 0,
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),
InsertMonth = Table.AddColumn(InsertYear, "MonthOfYear", each Date.Month([Date]), type number),
InsertMonthName = Table.AddColumn(InsertMonth, "Month Name", each Text.Proper( Date.ToText([Date], "MMMM")), type text),
InsertCalendarMonth = Table.AddColumn(InsertMonthName, "Month & Year", each [Month Name] & " " & Number.ToText([Year]), type text),
InsertDayWeek = Table.AddColumn(InsertCalendarMonth, "DayOfWeek", each Date.DayOfWeek([Date]) + WDStart, Int64.Type),
InsertDayName = Table.AddColumn(InsertDayWeek, "DayOfWeekName", each Text.Proper( Date.ToText([Date], "dddd" )), type text),
InsertMonthnYear = Table.AddColumn(InsertDayName , "MonthnYear", each [Year] * 10000 + [MonthOfYear] * 100, type number),
InsertWeekNumber= Table.AddColumn(InsertMonthnYear, "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),
InsertWeeknYear = Table.AddColumn(InsertISOyear, "WeeknYear", each [ISO Year] * 10000 + [ISO Weeknumber] * 100, Int64.Type),
AddFY = Table.AddColumn(InsertWeeknYear, "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),
CurrentDateRecord = Table.SelectRows(AddFY, each ([Date] = CurrentDate)),
CurrentDay = CurrentDateRecord{0}[Date],
CurrentWeek = CurrentDateRecord{0}[WeeknYear],
CurrentMonth = CurrentDateRecord{0}[MonthnYear],
CurrentFY = CurrentDateRecord{0}[Fiscal Year],
CurrentYear = CurrentDateRecord{0}[Year],
InsertCurrentFY = Table.AddColumn(AddFY, "IsCurrentFY", each if [Fiscal Year] = CurrentFY then true else false, type logical),
InsertCurrentMonth = Table.AddColumn(InsertCurrentFY, "IsCurrentMonth", each if [MonthnYear] = CurrentMonth then true else false, type logical),
InsertCurrentWeek = Table.AddColumn(InsertCurrentMonth, "IsCurrentWeek", each if [WeeknYear] = CurrentWeek then true else false, type logical),
InsertCurrentDay = Table.AddColumn(InsertCurrentWeek, "IsCurrentDay", each if [Date] = CurrentDay then true else false, type logical)
in
InsertCurrentDay, documentation = [
Documentation.Name = "",
Documentation.Description = " ",
Documentation.Category = "",
Documentation.Version = "",
Documentation.Author = ""]
in
Value.ReplaceType(fnDateTable, Value.ReplaceMetadata(Value.Type(fnDateTable), documentation))
However, I would like to add another field that identifies if the date record is within the current 6 months.
This should be dynamic and adjust to the day/date you look at the data.
The 6-month period should be based on financial years.
From April-September
And October to March.
How I want it to work:
If I look at the date table today (18/8/2021)
And I had a date record. For example, on the 1/10/2021 then that would be flagged as 0 (as it is not currently within the defined period (April-September). If I had a record with a date of the 1/4/2021 this would flag as 1 as it was in the defined period.
However, if I look at the date table on the 31/12/2021. I would expect the record that was flagged as 0 (1/10/2021) to now show as 1 and the record with a date of the 1/4/2021 to show as 0.
Does anyone know how to achieve this?
Thanks,
Boycie92
