New calculated column from input data

Hi v-yingjl  and amitchandak 

Thank you for your help, I’m very new on Power Bi and Data shaping and this community is very useful, I found a thread with a similar question, and I tried to adapt the solution on my project using also amitchandak solution. I don’t know if it is the best solution but for the moment it seems to be working.

I create a new input table

Table1

Where all columns are input data (not imported from DB).

Items are the same of DB, Time and costs are not in the production DB.

Then I create two new columns on the production table:

Data_MP = CALCULATE(MAX(Table1[Date_Item]),filter(Table1,Table1[Item]=earlier([Item])&&Table1[Date_Item]<=EARLIER([time])))

Price_MP = LOOKUPVALUE(Table1[Price],[item],[item],Table1[Date_Item],[Data_MP])

Then I create a measure Weight x Price MP to calculate the cost of every batch.

Now I must do two improvements:

• I’m trying to figure out how to import in the TABLE1 the first column Items from DB where there is also the list of Items updated. And continue to input the second and third column (date and price). This would be useful to avoid forgetting a new item.
• Record in DB are automatically deleted after 1 year and 6 month, I would like to record also older data