cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Post Partisan

## Get last 2 transactions

Hello,

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

3 ACCEPTED SOLUTIONS
Resolver II

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

Super User

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

Community Support

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.

16 REPLIES 16
Community Support

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.

Post Partisan

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

Super User

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

Super User

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

Post Partisan

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

Post Partisan

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.

Super User

Post Partisan

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)

Super User

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

Post Partisan

Hi @Ahmedx ,

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

Resolver II

Post Partisan

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.

Resolver II

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

Post Partisan

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?

Resolver II

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

Post Partisan

Thanks @Jamie_Scott ! It works.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors