Filter between tables

[PowerMyBI]

Hi Power BI Community,

I am trying to sum and filter results between two tables. The result we are hoping to achieve is to sum the available hours in Table1 based on if the staff member is employed. I'm uncertain if I can do this in a calculated column or if I need to do so in a separate table.

Table1 (EndDate is empty if staff is stil currently employed)

StaffName	StartDate(d/mm/yy)	EndDate(d/mm/yy)	HoursAvailable
Sally	13/04/2018	15/09/2020	40
Bob	1/06/2019		35
Fred	25/10/2020		37

Table2

RevenueTotal	Month	CalculatedColumnOfAvailableHours
575000	08/2020	75
550000	09/2020	75
592000	10/2020	72

Thanks!

[amitchandak · Accepted solution]

@PowerMyBI , Create a new column in table 2

sumx(filter(Table1, Table1[start date] <= Table2[Month] && (isblank(Table1[EndDate]) || Table1[EndDate] >=Table2[Month])),[HoursAvailable])

[PowerMyBI]

Yes this worked perfectly thank you @amitchandak ! I see what I was doing wrong in my first attempts now.