Latest Unit Price for selected period

[amilafdo]

Hi All,

I wanted to display the latest price for the selected period with the vale. If anyone know this please help me to resolve.

Below are the example table. If I select the Apr 2017 BCD value should be  -> Qty 250, Value 500, Price 2. If I select the Jan 2018 BCD value should be -> Qty 200, Value 500, Price 2.5 . If I select both 2017 & 2018 BCD value should be -> Qty 450, Value 1000, Price 2.5 (Latest price).

Price Table:

Item	DATE	Price
ABC	1/1/2017	3.5
BCD	1/1/2017	2
DEF	1/1/2018	3
ABC	1/1/2018	2.7
BCD	1/1/2018	2.5

Sales Table: 

Item	DATE	Qty	Value
ABC	10/1/2017	100	350
BCD	4/1/2017	250	500
DEF	1/1/2018	25	75
ABC	1/1/2018	350	945
BCD	1/1/2018	200	500

Hope my requirement is clear. If you need further clarification please let me know.

Thanks

Amila Fernando

[tresesco]

I guess you would need: FirstSortedValue()

[amilafdo]

Hi Tresesco B,

Thanks for your reply, I had tried with FirstSortedValue but I couldn't filter for date from Sales table.

Thanks

Amila

[tresesco]

How are you making the data model? Date fields from two tables should not be associated, I guess. You should probably just link tables only with Item field.

[amilafdo]

Hi Tresesco B,

Can you please guide me how I can connect the 2 tables & FirstSortedValue() expression.

Thanks

Amila

[tresesco]

A deeper look at your tables says that it is possibly a SCD issue which could be resolved using intervalmatch() in the modelling like said here:IntervalMatch and Slowly Changing Dimensions

[amilafdo]

Hi,

Any feedback for the above requirement. Provided link doesn't work.

Regards

Amila