Identify values before, close to and after today

Hi Anonymous ,

 

Try this updated code and see if it works better for you:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("rdZBS8MwGAbgvxJ6XumXL23aHlUUPIwdPcQdVOrFgSJO2L832aRrmE3emlw+tpL36aDvkhpTyIpUxcRcrIrN/ku8v4qbw8tusF9vN+tvKQw35d3wXDJv7TWp3UJuKuJTarsyBU+Mh2F42x3GD8IQl+unzzGuXHxc79Jq/hfARp3BaDIYOoPRZjC6DEafwZB0UQ3xuCdiLe6v/7gkjCSPrN0k3wxU9l8eZ/bihV7mxcu9zIsXfZmnQ39/qb18c5x+Hig8oACVBxSg9HGFKYsS7zmixNuNKCr0jFl5ee22AeXngQ0aUIAtGlCATRpQgNYCCtBaQJm21p7ap2mTvZdsXbL3kirQVJQIv0EghBXqNIGTBZUs1MlCkyzoiXD5JqbLq49zvLPzvP73zWPu/rDRTYzjEe8OEeq8VG9n56X6+TtDeXckpQGBDmJAoIIYoELPTqppvCU3lZ8P9G+BEuggqGx/AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, #"Pay Run" = _t, #"Processed On" = _t, #"Processed On Order" = _t, #"Pay Run Date" = _t]),
    chgTypes = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Pay Run", type text}, {"Processed On", type text}, {"Processed On Order", Int64.Type}, {"Pay Run Date", type date}}),
    Date.Today = Date.From(DateTime.LocalNow()),
    Order.Today = Table.SelectRows(chgTypes, each [Date] = Date.Today){0}[Processed On Order],
    addOutput =
    Table.AddColumn(chgTypes, "output", each
        if [Processed On Order] = Order.Today then "Most Recent"
        else if Number.From([Processed On Order]) = Number.From(Order.Today) + 1 then "Current"
        else if Number.From([Processed On Order]) = Number.From(Order.Today) + 2 then "Future"
        else null
    )
in
    addOutput

 

The Date.Today and Order.Today steps are just standalone steps that perform specific functions outside of changing the main table.

 

Date.Today = Declares the value of today's date to make subsequent references quicker and tidier to write, so I can just write 'Date.Today' in code, instead of 'Date.From(DateTime.LocalNow())' every time I want to use today's date.

 

Order.Today = Selects the value of [Processed On Order] at today's date to use as a comparison to help allocate the three different ouput statuses. The Table.SelectRows bit gets any rows that have today's [Date] on them, the '{0}' bit gets the first row out of these rows, and the [Processed On Order] bit at the end selects just that column, so it zeroes in on just a single cell value that can be used in other calculations.

 

Pete