Power Query Date Table: Current 6 Months

[Boycie92]

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

[lbendlin]

"This should be dynamic and adjust to the day/date you look at the data"

That means you would need a measure

Measure = TODAY()

It also means that you cannot create a flag based on this. Measures cannot impact columns. They can only impact other measures. 

Consequently you would need to implement your entire logic through measures.  Possible, but rather inefficient.

If you let go of your first requirement and allow this to be a calculated column (only refreshed when dataset is updated) then everything would become much easier.

[Boycie92]

HI @lbendlin 

I have somthing simialr built into the M code:     CurrentDateRecord = Table.SelectRows(AddFY, each ([Date] = CurrentDate))

Would that not work?

If it has to be a caculated column then I would be ok with that.

what formual would I need to use for it?

Thanks,

Boycie92

[lbendlin]

Doing it in M code is the same (sort of) as doing it in a calculated column. It is only computed once during the dataset refresh.  If you do not refresh your dataset frequently then the meaning of "CurrentDate"  will get skewed as you move on from the date when the dataset was last refreshed.

So yes, your approach would work, but it would not be dynamic.

[Boycie92]

Hi@lbendlin,

 My dataset would be getting refreshed everyday. So I am not concenred about that. I am just confused as to how I can build that flag in and get it to work the way I want.

Thanks,

Boycie92

[lbendlin]

When does your fiscal year start, in April or October?

[Boycie92]

Hi @lbendlin 

It starts in April.

Thanks,

Boycie92