Hello! New to PowerBi.... searched extensively but cannot find a solution!
I'm trying to create a table or some sort of visualization in PowerBi where I can see an employee's capacity on any given date.
I have two tables:
Table A (Projects) has the following columns: Project Name, Employee Name, Project Start Date, Project End Date, Workload (that the project is taking up of the employee's time)
Table B (Dates) - a list of dates from 1/1/2023 to 12/31/2026
Table A
| Project Name | Employee Name | Project Start Date | Project End Date | Workload |
| ProjectA | John | 1/1/2024 | 1/30/2024 | 30% |
| ProjectB | John | 1/15/2024 | 2/15/2024 | 40% |
| ProjectC | Nancy | 3/1/2024 | 3/31/2024 | 15% |
The table I want:
| Date (every date of the year... just picking random dates to demonstrate goal) | John's Total Workload | Nancy's Total Workload |
| 12/31/2023 | 0% | 0% |
| 1/1/2024 | 30% | 0% |
| 1/20/2024 | 70% | 0% |
| 3/15/2024 | 0% | 15% |
I'd like to be able to see what an employee's workload looks like on any given day.
Here's what I've tried:
WorkloadByEmployee =
var AllEmployees = Values('Projects'[EmployeeName])
var AllDates = Values('Dates'[Date])
return
addcolumns(crossjoin(AllEmployees, AllDates),
"TotalWorkload",
Calculate(
SUM('Projects'[Workload]),
USERELATIONSHIP('Projects'[EndDate], 'Dates'[Date]),
FILTER('Projects',
NOT ISBLANK('Projects'[StartDate]) &&
NOT ISBLANK('Projects'[EndDate]) &&
NOT ISBLANK('Projects'[EmployeeName]) &&
'Projects'[StartDate] <= MAX('Dates'[Date]) &&
'Projects'[EndDate] >= MAX('Dates'[Date]) &&
'Projects'[EmployeeName] = EARLIER('Projects'[EmployeeName])
)))
This returns a table with Date, EmployeeName, and Workload, but workload is blank. I've ensured the many to one relationship between Project[StartDate] and Dates[Date] is active. The many to one relationship between Project[EndDate] and Dates[Date] is inactive. I've checked all the values to make sure they're in the right format. Please let me know if you have a better solution or if you know of one out there! Thank you!!
