Power Query Date Table: Current 6 Months

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