Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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 th...
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
Jihwan_Kim
Icon for Super User rankSuper User
5 years ago

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