Get a customer's penultimate sales date

[RichardSm]

I have a sales table that has all sales with date and customer information. I referenced this table and grouped the sales by customer and I needed to get the penultimate sales date. I can't think of a way with better performance, I tested it with gpt, but it had horrible performance.

[lbendlin · Accepted solution]

Do a summarize by date. Grab the TOPN 2 DESC.  from that grab the TOPN 1 ASC.

[v-jingzhan-msft · Accepted solution]

Hi @RichardSm 

@lbendlin provides a DAX solution idea. If you want a Power Query solution, you can try my approach as below.

I group the data by customer, then sort by date descendingly, and add an Index column then. The row with number 2 is the penultimate sales date record for each customer. 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bc1BCsAgDATAv+QspFkjxaP6DPH/36gVA430sodh2e2dhMG4oBSolBlCI7yafhQcTWv9dLNTXRqPhbQVatzaDCxWFji+9x+iYzuUfI6MBw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Customer = _t, Sales = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Customer", type text}, {"Sales", Int64.Type}}),
    // Group by customer, sort by date descendingly, add an Index column
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Customer"}, {{"AllData", each Table.AddIndexColumn(Table.Sort(_, {"Date", Order.Descending}), "SalesDateIndex", 1, 1), type table [Date=nullable date, Customer=nullable text, Sales=nullable number]}}),
    #"Expanded AllData" = Table.ExpandTableColumn(#"Grouped Rows", "AllData", {"Date", "Sales", "SalesDateIndex"}, {"Date", "Sales", "SalesDateIndex"})
in
    #"Expanded AllData"

You can then use a DAX formula to get the penultimate date for each customer where its index is 2. For instance,

penultimate date = CALCULATE(MAX('Table'[Date]),'Table'[SalesDateIndex]=2)

This may calculate faster. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[lbendlin]

A better solution is to use OFFSET or INDEX window functions.

[lbendlin · Accepted solution]

Do a summarize by date. Grab the TOPN 2 DESC.  from that grab the TOPN 1 ASC.