Forum Discussion

caseski's avatar
caseski
Icon for Helper I rankHelper I
5 years ago
Solved

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

    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]

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

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's avatar
caseski
Icon for Helper I rankHelper I
5 years ago

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