Correct cost date

[Anonymous]

Hello,

I have two different tables, historical cost items, and item movements.

The cost of items is updated over time:

I have item movements over time. How can I calculate a measure that gives me the correct cost date by quantity?

Example:

Thanks for the help.

[Jihwan_Kim]

Hi, @Anonymous 

Please check the below picture and the sample pbix file's link down below.

I tried to add some more data and created a sample like below.

Costs =

IF (

NOT ISBLANK ( [Qty Total] ) && ISFILTERED ( Dates ),

CALCULATE (

LASTNONBLANKVALUE ( Costs[Date], SUM ( Costs[Cost] ) ),

FILTER ( ALL ( Dates ), Dates[Date] <= MAX ( Dates[Date] ) )

)

)

https://www.dropbox.com/s/wnlurhlij28thi1/descud.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM

[amitchandak · Accepted solution]

@Anonymous , A new column in table 2

new column =
var _1 = maxx(filter(Table1, Table1[Date] <=Table2[Date] && Table1[Item] = table2[item]), Table1[Date])
return
maxx(filter(Table1, Table1[Date] = _1 && Table1[Item] = table2[item]), Table1[Cost])