Get last 2 transactions

[Oros]

Hello,

I have one table that has transactions for several products.  How do you separately get the last 2 transactions for each specific items?

The transaction id is unique and increments for all products.  Thanks.

[Jamie_Scott · Accepted solution]

I think you'll need to create a rank column for the transactions by product;

Rank =
RANKX( FILTER( ALL('Sales'),
'Sales'[Product] = EARLIER('Sales'[Product])),
'Sales'[Transaction Date],, DESC, Dense )

Then for each of the other components, you can create additional columns; based on rank. You might need to adjust max to sum or whichever other aggregate you use for quantity.

Quantity #1 = 
CALCULATE(MAX('Sales'[Quantity]),
FILTER(
'Sales',
'Sales'[Product] = EARLIER('Sales'[Product]) && 'Sales'[Rank] = 1))

Expiry Date #1 = 
CALCULATE(MAX('Sales'[Expiry Date]),
FILTER(
'Sales',
'Sales'[Product] = EARLIER('Sales'[Product]) && 'Sales'[Rank] = 1))

and then create the same for #2, just changing the rank, then you can create your matrix

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[v-xinruzhu-msft · Accepted solution]

Hi, 

Thanks for the solutions @Jamie_Scott  and @Ahmedx provided, and i want to offer some more information for user to refer to.

hello @Oros , based on your description, you can refer to the following sample.

Sample data :

You can create the following measures

Experdate1 =
VAR a =
    SUMMARIZE (
        TOPN (
            2,
            FILTER ( ALLSELECTED ( 'Table' ), [Product] IN VALUES ( 'Table'[Product] ) ),
            CALCULATE ( MAX ( 'Table'[Transaction#] ) ), DESC
        ),
        [Transaction#]
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Expiry] ),
        'Table'[Transaction#] = MINX ( a, [Transaction#] )
    )

Experdate2 =
VAR a =
    SUMMARIZE (
        TOPN (
            2,
            FILTER ( ALLSELECTED ( 'Table' ), [Product] IN VALUES ( 'Table'[Product] ) ),
            CALCULATE ( MAX ( 'Table'[Transaction#] ) ), DESC
        ),
        [Transaction#]
    )
RETURN
    CALCULATE (
        MAX ( 'Table'[Expiry] ),
        'Table'[Transaction#] = MAXX ( a, [Transaction#] )
    )

Qty_1 = CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Expiry]=[Experdate1]))

Qty_2 = CALCULATE(SUM('Table'[Qty]),FILTER('Table','Table'[Expiry]=[Experdate2]))

Output

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Oros]

Hi @v-xinruzhu-msft , This works as well.  Thanks!

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[Ahmedx · Accepted solution]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

[Oros]

Hi @Ahmedx ,  just a little bit of tweak as you advised...it works!  Thanks.

[Oros]

Hi @Ahmedx ,

Thank you for your quick reply.  Is your solution based on the transaction id or expiration dates? Your pbix files works, but when I applied the measures to my data, I get a repetitive result.
Thanks again.

[Ahmedx]

I don’t see the product in your screenshot, attach your file to help you

[Oros]

Hi @Ahmedx ,

Thanks again for your reply.  I can't a way to attach the sample pbix.

It looks like the measures are NOT based on the last 2 transactions for each product.

This is the undesired result

But the result should be based on the last 2 transactions for each product (example, product 1211)

[Ahmedx]

since you have a duplicate date you need to add an index column in power query.
can you do it?

[Oros]

Hi @Ahmedx ,

Thank you so much for your help.  Unfortunately I have never done an index.

[Jamie_Scott]

Check solution below and it'll answer your question. 

[Oros]

Hi @Jamie_Scott ,

Your solution is very close...one thing that I noticed is that the Ranking is based on the expiry date and NOT on the transaction #.  This is the result of your solution.

Product	Transaction	Qty	Expiry
844477	0050	30	January 9, 2025
844477	0055	75	April 30, 2024
844477	0049	10	September 28, 2024

Based on the table above, the correct result should be transactions 0050 and 0055 (January 9 and April 30). How do we adjust the RANK measure to reflect the transaction numbers instead?

Thanks again.

[Jamie_Scott]

Rank =
RANKX(
FILTER( ALL('Sales'), 'Sales'[Product] = EARLIER('Sales'[Product])),
'Sales'[TransactionID],,
DESC,
Dense
)

[Oros]

Hi @Jamie_Scott ,

Thank you for your quick reply.  Do I need to adjust the QTY and EXPIRY measures as well?  If yes, what should be the modification?  

[Jamie_Scott · Accepted solution]

I think you'll need to create a rank column for the transactions by product;

Rank =
RANKX( FILTER( ALL('Sales'),
'Sales'[Product] = EARLIER('Sales'[Product])),
'Sales'[Transaction Date],, DESC, Dense )

Then for each of the other components, you can create additional columns; based on rank. You might need to adjust max to sum or whichever other aggregate you use for quantity.

Quantity #1 = 
CALCULATE(MAX('Sales'[Quantity]),
FILTER(
'Sales',
'Sales'[Product] = EARLIER('Sales'[Product]) && 'Sales'[Rank] = 1))

Expiry Date #1 = 
CALCULATE(MAX('Sales'[Expiry Date]),
FILTER(
'Sales',
'Sales'[Product] = EARLIER('Sales'[Product]) && 'Sales'[Rank] = 1))

and then create the same for #2, just changing the rank, then you can create your matrix

[Oros]

Thanks @Jamie_Scott ! It works.