Forum Discussion
Filter between tables
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!
PowerMyBI , Create a new column in table 2
sumx(filter(Table1, Table1[start date] <= Table2[Month] && (isblank(Table1[EndDate]) || Table1[EndDate] >=Table2[Month])),[HoursAvailable])
2 Replies
PowerMyBI , Create a new column in table 2
sumx(filter(Table1, Table1[start date] <= Table2[Month] && (isblank(Table1[EndDate]) || Table1[EndDate] >=Table2[Month])),[HoursAvailable])
Yes this worked perfectly thank you amitchandak ! I see what I was doing wrong in my first attempts now.
