Custom column that compares multiple dates

[Moragtao]

Hello hello,

I need help. I'm attempting to make a custom column that compares both todays date, and a date in a specific column, to date ranges and returns either true or false depending. Below, you'll see an example of what it would look like not in Power Query. Both Today and NADALastUpdatedDate are the names of columns:

if Today >= 1/1 and <= 4/30 then
{
     if NADALastUpdatedDate <= 1/1 and >= 4/30 then
     {
          return True
     }
}
else if Today >= 5/1 and <= 8/31 then
{
     if NADALastUpdatedDate <= 5/1 and >= 8/31 then
     {
          return True
     }
}
else if Today >= 9/1 and <= 12/31 then
{
     if NADALastUpdatedDate <= 9/1 and >= 12/31 then
     {
          return True
     }
}


else
{
     return False
}

It's important that the year is neglected in this formula, because I'm trying to do this in a way where I won't need to update it every year. Does anyone have any ideas?

 

[Rudi]

I'm took a stab at this, but you'll need to double check if the output is accurate and producing expected results...

BTW: It makes more sense to have an 'OR' operator for conditions where a date is: (<=...) or (>=...).
I modified this in the M-code below, but adjust as necessary.

You will notice I added two 'helper' columns ("D1-YMD" and "D2-YMD") to produce a date where the year is 0001 (to allow evaluation on a consistent year value, effectively eliminating the year part of the date when the conditions are evaluated).

PS: I hope the date formats don't play havoc with you. Being from RSA, we use YMD formats so this is built into the solution code below. If your date format is different you might need to adapt parts of the code.

Hope it helps or guides you to a better solution.

let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Today", type date}, {"NADALastUpdatedDate", type date}}), 
    #"Inserted TAD1" = Table.AddColumn(#"Changed Type", "D1-YMD", each "0001/" & Text.AfterDelimiter(Text.From([Today], "en-ZA"), "/", 0), type text),
    #"Inserted TAD2" = Table.AddColumn(#"Inserted TAD1", "D2-YMD", each "0001/" & Text.AfterDelimiter(Text.From([NADALastUpdatedDate], "en-ZA"), "/", 0), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted TAD2",{{"D1-YMD", type date}, {"D2-YMD", type date}}),
    #"Added Conditional Column" = Table.AddColumn(#"Changed Type1", "Evaluate", each 
        if ([#"D1-YMD"] >= #date(1,1,1) and [#"D1-YMD"] <= #date(1,4,30)) and ([#"D2-YMD"] <= #date(1,1,1) or [#"D2-YMD"] >= #date(1,4,30)) then "true" else 
        if ([#"D1-YMD"] >= #date(1,5,1) and [#"D1-YMD"] <= #date(1,8,31)) and ([#"D2-YMD"] <= #date(1,5,1) or [#"D2-YMD"] >= #date(1,5,31)) then "true" else
        if ([#"D1-YMD"] >= #date(1,9,1) and [#"D1-YMD"] <= #date(1,12,31)) and ([#"D2-YMD"] <= #date(1,9,1) or [#"D2-YMD"] >= #date(1,12,31)) then "true" else
        "false")
in
    #"Added Conditional Column"

 

[Moragtao]

Your idea of changing all the years to 1 was absolutely genius!

Thank you very much