How to sum multiple rows in one table and add total to another table with conditions

[Sha]

I have 2 tables.  I want to add a column to the job history table that sums the transaction amt from the Transaction table for the user and where the transaction date is within the job history date range.

Transaction table:

user   transdate    amt

1        1/1/22        100

1        2/1/22        100

1        5/1/22        150

2        1/1/22        100

2        2/1/22        100

2        5/1/22        150

Job History table:

user   startdate    enddate   job

1        1/1/22        3/31/22      a

1        4/1/22        null             b   

2        1/1/22        null             a

End result

user   startdate    enddate   job       amt

1        1/1/22        3/31/22      a        200

1        4/1/22        null             b       150

2        1/1/22        null             a        350

Thank you!

[ryan_mayu · Accepted solution]

@Sha 

pls try to create a column

Column = 
if(ISBLANK('job History'[enddate]),sumx(FILTER('Transaction','Transaction'[user]='job History'[user]&&'job History'[startdate]<='Transaction'[transdate]),'Transaction'[amt]),sumx(FILTER('Transaction','Transaction'[user]='job History'[user]&&'job History'[startdate]<='Transaction'[transdate]&&'job History'[enddate]>='Transaction'[transdate]),'Transaction'[amt]))

pls see the attachment below

[Sha]

Thank you so much, I see what I did wrong now.  It works perfectly.

[ryan_mayu]

you are welcome