Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to 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 , 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.
User | Count |
---|---|
126 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |