Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Oros
Post Partisan
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?
The transaction id is unique and increments for all products.  Thanks.
Oros_0-1712769258249.png
Oros_1-1712769298189.png

 

3 ACCEPTED SOLUTIONS
Jamie_Scott
Resolver II
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

View solution in original post

Ahmedx
Super User
Super User

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

View solution in original post

v-xinruzhu-msft
Community Support
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 :

vxinruzhumsft_0-1713158729329.png

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

vxinruzhumsft_3-1713159931590.png

 

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.

 

View solution in original post

16 REPLIES 16
v-xinruzhu-msft
Community Support
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 :

vxinruzhumsft_0-1713158729329.png

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

vxinruzhumsft_3-1713159931590.png

 

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.

 

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

Ahmedx
Super User
Super User

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

Ahmedx
Super User
Super User

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

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

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.

 

Oros_0-1712775790315.png

 

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

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

Oros_0-1712839077642.png

 

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

Oros_1-1712839129130.png

 

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

Hi @Ahmedx ,

 

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

Check solution below and it'll answer your question. 

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.

 

Oros_0-1712843549641.png

 

 

ProductTransactionQtyExpiry
844477005030January 9, 2025
844477005575April 30, 2024
844477004910September 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.

 

 

 

 

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

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?  

 

Oros_0-1712846823634.png

 

Jamie_Scott
Resolver II
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

Thanks @Jamie_Scott ! It works.

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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