New calculated column from input data

[caseski]

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)?

[v-yingjl]

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.

[caseski]

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

[amitchandak · Accepted solution]

@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]

[caseski]

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