Forum Discussion
Correct cost date
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.
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])
2 Replies
- amitchandak
Super User
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]) - 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] ) )))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
