SUM RESULTS FROM A TABLE IN ANOTHER TABLE IN POWER BI USING 3 DIFFERENT RELATIONSHIPS

[SCNCKS1]

hi, i hope someone can help.

i am trying to create a calculated column that returns the sum of results from a table that is not directly related in the model.  I have used treatas, but it isn't working correctly. Also, not sure how to incorporate the greater than function for the date. in the filter. There are more columns in both tables, but the ones showing are the ones that would need to be matched to select the correct total.

Client Rates
rate	Description	production unit	effective date	projectid
2	Description1	ProductionUnit1	4/8/2022	Project1
5	Description2	ProductionUnit1	4/8/2022	Project1

Production
Production Unit	Date	projectid	Client Rate Sum
ProductionUnit1	greater than or equal to C_R_Effective_Date	Project1	7

Thank you!

[amitchandak · Accepted solution]

@SCNCKS1 , if you are creating a column then you can have

new column in table2

sumx(filter(Table1, Table2[Date] > Table1[Date] && Table2[col] = table1[col]), Table1[Value])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[amitchandak · Accepted solution]

@SCNCKS1 , if you are creating a column then you can have

new column in table2

sumx(filter(Table1, Table2[Date] > Table1[Date] && Table2[col] = table1[col]), Table1[Value])

refer 4 ways (related, relatedtable, lookupvalue, sumx/minx/maxx with filter) to copy data from one table to another
https://www.youtube.com/watch?v=Wu1mWxR23jU
https://www.youtube.com/watch?v=czNHt7UXIe8

[SCNCKS1 · Accepted solution]

Worked perfectly! Thank you!