Create a table that provides sum of workload if project is active on a given date

Anonymous — Thu, 12 Dec 2024 23:12:47 GMT

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!!

Re: Create a table that provides sum of workload if project is active on a given date

lbendlin — Thu, 12 Dec 2024 23:45:33 GMT

see attached

Re: Create a table that provides sum of workload if project is active on a given date

ThxAlot — Fri, 13 Dec 2024 17:22:26 GMT

Re: Create a table that provides sum of workload if project is active on a given date

Anonymous — Fri, 13 Dec 2024 17:35:09 GMT

Incredible! Thank you for this solution!

Re: Create a table that provides sum of workload if project is active on a given date

Anonymous — Fri, 13 Dec 2024 17:35:47 GMT

This worked, too! Thank you so much!

topic Re: Create a table that provides sum of workload if project is active on a given date in DAX Commands and Tips

Create a table that provides sum of workload if project is active on a given date

Re: Create a table that provides sum of workload if project is active on a given date

Re: Create a table that provides sum of workload if project is active on a given date

Re: Create a table that provides sum of workload if project is active on a given date

Re: Create a table that provides sum of workload if project is active on a given date