Forum Discussion
Show values between dates selection
Hi all,
I have a report page which shows Item sales details. Now I want to map cost of the item on each line.
The Cost table is like as follows:
| ItemNumber | PostingDate | UoM | Cost |
| 3002383 | 02/01/2022 | PC | 0.090 |
| 3002383 | 05/01/2022 | PC | 0.095 |
| 3002383 | 12/01/2022 | PC | 0.100 |
| 3002383 | 13/01/2022 | PC | 0.960 |
If I run the Sales report from 02/01/2022 to 12/01/2022, the cost for the aboive item should show as
from 02/01/2022 to 04/01/2022: Cost: 0.090
from 05/01/2022 to 11/01/2022: Cost: 0.095
on 12/01/2022: the cost is 0.100
i did the data transform in PQ, pls see if this is what you want
6 Replies
you can create a measure
Measure =VAR _date=max('date'[Date])VAR _date2=maxx(FILTER('Table','Table'[PostingDate]<=_date),'Table'[PostingDate])return maxx(FILTER('Table','Table'[PostingDate]=_date2),'Table'[Cost])pls see the attachment belowryan_mayu thank you for the quick response. my requirement is to show the cost price on respective sales line.
Date Item Quantity Cost Total Cost Selling Price Total Margin 02/01/2024 3002383 2 0.09 0.18 0.115 0.23 0.05 03/01/2024 3002383 1 0.09 0.09 0.115 0.115 0.025 05/01/2024 3002383 10 0.095 0.95 0.115 1.15 0.2 10/01/2024 3002383 5 0.095 0.475 0.115 0.575 0.1 12/01/2024 3002383 2 0.1 0.2 0.115 0.23 0.03 i did the data transform in PQ, pls see if this is what you want
Hi ryan_mayu Thank you, can u please share the .pbix file once again as I am getting error while opening it.
i forgot to delete the visual. The output is the 'Merge' table.
ryan_mayu Thank you