Forum Discussion
New calculated column from input data
Dear all, I’d like to transform a production report to a cost management report.
Data are imported from production DB (SQL DB), below an example table:
CUSTOMER | PRODUCT | TIME | ITEM | WEIGHT |
ALFA | PIPPO | 06/07/2021 12.00 | SUGAR | 3 |
ALFA | PIPPO | 06/07/2021 12.05 | MILK | 2 |
ALFA | PIPPO | 06/07/2021 12.08 | EGGS | 1 |
BETA | PLUTO | 06/07/2021 12.12 | MILK | 2 |
BETA | PLUTO | 06/07/2021 12.15 | EGGS | 4 |
BETA | PLUTO | 06/07/2021 12.20 | SUGAR | 3 |
I’d like to calculate the cost of every product as result of: ‘Weight’ x ‘ITEM_PRICE’.
The problem is that DB doesn’t have the ITEM_PRICE so I should input it assuming that costs changes every month (remain constant for a month).
As there are thousands of rows but items are a few (about 10) I assume that I could input ITEM_PRICE in a new table and then merge the new table to the production one.
I'd like to import the ITEM from DB and input only the cost of every Item every month.
Something like the table below:
ITEM* | TIME* | COST (input data) |
SUGAR | JUNE | 120 |
MILK | JUNE | 140 |
EGGS | JUNE | 90 |
SUGAR | JULY | 122 |
MILK | JULY | 142 |
EGGS | JULY | 92 |
*from DB
What do you think? is it correct?
How can I generate a new table importing Item and how can I input Item_cost and then merge the two tables in order to create the measure (WEIGTH x ITEM COST)?
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]
4 Replies
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]
Thank you amitchandak the second table doesn't exist, I must create it as a new table but ITEM should be the same of Table1, is it possible?
I wanna input only the ITEM_COST every month
Hi 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.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
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
