How to show the last five transactions dynamically ?

[bourne2000]

Hi

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

Invoice number	Invoice Date	Product	Invoice Amount
20A1	01.10.2021	Jam	500
20A2	02.10.2021	Bread	100
21B2	01.10.2021	Bread	120
22C3	03.10.2021	Bread	14
24C5	04.10.2021	Bread	24
24C6	05.10.2021	Jam	29
24C7	06.10.2021	Jam	500
27a3	07.10.2021	Jam	500
28d2	08.10.2021	Jam	93
29f3	09.10.2021	Jam	192
29r3	10.10.2021	Jam	10
24hc	11.10.2021	Bread	2
25hc	05.10.2021	Bread	102
278A	04.10.2021	Bread	102
293E	11.10.2021	Bread	10
324R	12.10.2021	Choclate	3
33C3	09.10.2021	Choclate	3
35R3	05.10.2021	Choclate	5

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

[v-henryk-mstf]

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.

[Jihwan_Kim · Accepted solution]

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 ) )