Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
147 | |
107 | |
105 | |
87 | |
61 |