Forum Discussion
Helper INew calculated column from input data
caseski , If the time in the second table can be the date, any date in a month then we can create a new column
A new column in Table 1
maxx(filter(Table2, Table2[item] = Table1[item] && eomonth(Table2[time]) = eomonth(Table1[time])), Table2[Cost]) * Table1[Weight]
or
AverageX(filter(Table2, Table2[item] = Table1[item] && eomonth(Table2[time]) = eomonth(Table1[time])), Table2[Cost]) * Table1[Weight]
Community SupportHi caseski ,
Since there are not products in the second table, how would you calculate it based on products? In other words, how would you group the table by products to calculate?
Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Helper IHi 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
ITEM | DATE_ITEM | PRICE |
SUGAR | 01 June 2021 | 120 |
MILK | 01 June 2021 | 140 |
EGGS | 01 June 2021 | 90 |
SUGAR | 07 June 2021 | 122 |
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:
CUSTOMER | PRODUCT | TIME | ITEM | WEIGHT | DATA MP | PRICE MP |
ALFA | PIPPO | 03 June 2021 12.00 | SUGAR | 3 | 01 June 2021 | 120 |
ALFA | PIPPO | 03 June 2021 12.05 | MILK | 2 | 01 June 2021 | 140 |
ALFA | PIPPO | 03 June 2021 12.08 | EGGS | 1 | 01 June 2021 | 90 |
BETA | PLUTO | 03 June 2021 12.12 | MILK | 2 | 01 June 2021 | 140 |
BETA | PLUTO | 08 June 021 12.15 | EGGS | 4 | 01 June 2021 | 90 |
BETA | PLUTO | 09 June 2021 12.20 | SUGAR | 3 | 07 June 2021 | 122 |
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