Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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 ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
102 | |
84 | |
79 | |
70 |
User | Count |
---|---|
120 | |
110 | |
95 | |
82 | |
77 |