Forum Discussion

bourne2000's avatar
bourne2000
Icon for Helper V rankHelper V
4 years ago
Solved

How to show the last five transactions dynamically ?

Hi

 

I have a retail data which has mostly commodity product.  Example of data as follow,

 

Invoice numberInvoice DateProductInvoice Amount
20A101.10.2021Jam500
20A202.10.2021Bread100
21B201.10.2021Bread120
22C303.10.2021Bread14
24C504.10.2021Bread24
24C605.10.2021Jam29
24C706.10.2021Jam500
27a307.10.2021Jam500
28d208.10.2021Jam93
29f309.10.2021Jam192
29r310.10.2021Jam10
24hc11.10.2021Bread2
25hc05.10.2021Bread102
278A04.10.2021Bread102
293E11.10.2021Bread10
324R12.10.2021Choclate3
33C309.10.2021Choclate3
35R305.10.2021Choclate5

 

I want to show the product pricing trend for the last 5 invoices. All the invoice number is unique. For example, I need to create a simple bar graph for product vs Invoice amount for the last five invoices only. Some of the product like choclate has only one invoices, in that case it can show the one invoice. I have a product filter and date filter. I need to see the product pricing trend for last five invoices for any product dynamically. 

 

Can anyone advise me how to do that?

 

Attach sample pbix file https://we.tl/t-DY7ZAZkzfW

Need Help
Show and Tell
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I do not know how your desired outcome looks like, but please check the below picture and the sample pbix file's link down below.

     

     

    Invoice amount last 5 per product : =
    VAR _lastN = 5
    VAR _currentproduct =
    MAX ( 'Table'[Product] )
    VAR _topNtable =
    TOPN (
    _lastN,
    FILTER ( ALL ( 'Table' ), 'Table'[Product] = _currentproduct ),
    CALCULATE ( MAX ( 'Table'[Invoice Date] ) ), DESC
    )
    RETURN
    CALCULATE ( [Invoice amount measure :], KEEPFILTERS ( _topNtable ) )

     

     

     

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I do not know how your desired outcome looks like, but please check the below picture and the sample pbix file's link down below.

     

     

    Invoice amount last 5 per product : =
    VAR _lastN = 5
    VAR _currentproduct =
    MAX ( 'Table'[Product] )
    VAR _topNtable =
    TOPN (
    _lastN,
    FILTER ( ALL ( 'Table' ), 'Table'[Product] = _currentproduct ),
    CALCULATE ( MAX ( 'Table'[Invoice Date] ) ), DESC
    )
    RETURN
    CALCULATE ( [Invoice amount measure :], KEEPFILTERS ( _topNtable ) )

     

     

     

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi bourne2000 ,

     

    According to your description, you want to filter the Invoice Amount by dynamic dates, corresponding to the latest 5 dates under each Product. you need to create a separate date table. I have done the test with the following reference

    M = 
VAR sel_data =
    SELECTEDVALUE ( 'Table 2'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Table'[Invoice Amount] ),
        KEEPFILTERS (
            TOPN (
                5,
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    MAX ( 'Table'[Product] ) = 'Table'[Product]
                        && 'Table'[Invoice Date] < sel_data
                ),
                'Table'[Invoice Date], DESC
            )
        )
    )

     


    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


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