How to Calculate % of Work Days Planned Over Work Year

[elysesmith12]

Hello! 

I'm new to Power BI and need assistance calculating the % of a work year planned by an employee.

Here's how my data table is set up:

Employee	Project	Start	End	Duration Excl. Holidays/Weekends
Tom	A	8/1/22	8/1/22	1
Steph	B	12/13/22	12/14/22	2
Sally	C	5/2/2022	5/6/2022	5
Bob	D	8/24/2022	8/25/2022	2
Bob	E	8/22/2022	8/26/2022	5

As you can see in the table above, 1 person may be responsible for multiple projects that overlap work days. For Bob, he would have a total of 5 work days planned since his projects overlap the same days. How would I write a measure to calculate this?

I can't figure out how to only total the distinct calendar dates (I hope I'm explaining this correctly - please help!). 

Thank you in advance to those of you who read this and take time to share your expertise!

[selinaz · Accepted solution]

 Hi @elysesmith12 ,

Is this result you want ?

If it is, you can try the following meausre:

workdays = CALCULATE(DATEDIFF(SELECTEDVALUE(ProjectsAssigned[Start]),SELECTEDVALUE(ProjectsAssigned[End]),DAY))+1

% Work Year Planned = 'ProjectsAssigned'[workdays]/365.0 * 100

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

Best regards.

[technolog]

Hi!

You can create calculate table:

CalcTable = 

SUMMARIZE( 'Table', 

  'Table'[Employee],

  "@Diff", DATEDIFF('Table'[End], 'Table'[Start], DAYS) / 365.0 * 100

)

[elysesmith12]

Thank you for replying, I tried your suggestion and recieved the following error:

"A single value cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying and aggregation"

This is how my formula looks:

% Work Year Planned = SUMMARIZE('ProjectsAssigned',  'UserLookupAssignRef'[Name],  "@Diff", DATEDIFF('ProjectsAssigned'[EndDate], 'ProjectsAssigned'[StartDate], DAY)/365.0 * 100)

Any advice on what I need to do to fix?