Max date less than current record date

[shephee]

Hello, I need help with Power Query logic.

I have a table "asset" which contains asset values by account_no and by a calendar date.  I need to identify the previous calendar_date prior to the current calendar_date.  For example, in the table below if the current calendar_date is 9/26/2022, I need to return the prior date of 9/23/2022. I appreciate any help.

[BA_Pete]

Hi @shephee ,

The basic logic is MAX(Date) where Date < Current. When you say you want to "return" this date, what do you mean? Do you want to filter it, use it in a calculation, display t in a column etc.?

Pete

[shephee]

I want to display it in a new column.

[BA_Pete]

Okay, this sounds like an XY Problem.

Are you trying to bring the previous date/value into each row so you can do comparisons/variances between current row and prior?

Pete

[shephee]

Yes.  So what I would end up with is something that looks like this:

[BA_Pete · Accepted solution]

Cool, makes more sense 🙂

You can create two index columns, one starting from 1, the other from 0, and merge the table on itself using [account] & [Index0] = [account] & [Index1].

See working query below:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Zc3LDcAgDAPQXXJGimNKC7Mg9l+DlH44xMcny+5dzCNJUBSnEqSM9Ks1RY2a1Rj0Xlial9LzdHHs7qfeLVH97Qrqo2ivjgk=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [account = _t, calendar_date = _t]),
    addIndex1 = Table.AddIndexColumn(Source, "Index1", 1, 1, Int64.Type),
    addIndex0 = Table.AddIndexColumn(addIndex1, "Index0", 0, 1, Int64.Type),
    mergeOnSelf = Table.NestedJoin(addIndex0, {"account", "Index0"}, addIndex0, {"account", "Index1"}, "addIndex0", JoinKind.LeftOuter),
    expandCal_Date = Table.ExpandTableColumn(mergeOnSelf, "addIndex0", {"calendar_date"}, {"calendar_date.1"}),
    remOthCols = Table.SelectColumns(expandCal_Date,{"account", "calendar_date", "calendar_date.1"})
in
    remOthCols

Example query output:

Pete