Show values between dates selection

[shamnadkalathil]

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

[shamnadkalathil]

@ryan_mayu Thank you

[shamnadkalathil]

Hi @ryan_mayu Thank you, can u please share the .pbix file once again as I am getting error while opening it.

[ryan_mayu]

i forgot to delete the visual. The output is the 'Merge' table.

[shamnadkalathil]

@ryan_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

[ryan_mayu · Accepted solution]

i did the data transform in PQ, pls see if this is what you want

[ryan_mayu]

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 below