PQ Question - Add Conditional Column - Based off today's relative date

[scott_86_]

Hi,

For context, the date for today for this question is 15 Oct 2024 (plus 90 days is 13 Jan 2025).

In the sample snippet data below, I have an Expiry date column with past and future dates. The Number Outcome column is an example of what I am hoping to achieve with the new conditional column.

Could anyone help me out with adjusting the below code so that the x3 #date lines reflect the snippet in the 'add conditional column' below so that it utilises a PQ function instead of what I would normally use in Excel for this (the today function).

Although I had some initial success using Date.From(DateTime.LocalNow()), I was not able to adjust it further to add 90 days to this where necessary.

    Table.AddColumn(#"Extracted Date", "Expiry #",
    each if [Expiry] = null then 1
    else if [Expiry] <= #date(2024, 10, 15) then 4
    else if [Expiry] <= #date(2025, 1, 13) then 3
    else if [Expiry] > #date(2025, 1, 13) then 2
    else 0)

 

[AlienSx]

hi, use Date.AddDays to get today's date + 90 days.

let
    #"Extracted Date" = #table(type table [Expiry = date], {{#date(2024, 1, 1)}, {#date(2025, 1, 1)}, {#date(2026, 1, 1)}, {null}}), 
    tod = Date.From(DateTime.FixedLocalNow()), 
    tod90 = Date.AddDays(tod, 90), 
    col = Table.AddColumn(
        #"Extracted Date", 
        "Expiry #", 
        (x) => if x[Expiry] is null then 1 else {4, 3, 2}{List.PositionOf({tod, tod90, x[Expiry]}, x[Expiry], Occurrence.First, (c, v) => v <= c)}
    )
in
    col