Project staff planning - find employees who's FTE is more than 1.0

[scbjerre]

Hi there,

I've been struggling with this for a while so hopefully someone can help me.  I'm working on a project staffing dashboard who's source is a Sharepoint site. We staff in FTE values (1.0, 0.5, etc.) rather than hours. Our team works on multiple projects at a time, sometimes for years, other times for just a few weeks or months.  We'd like to get visibility into which employees are overallocated for any particular month.

I can't seem to figure out a way to "sum" or "count" the FTE values per user, per project over time. I don't want a total of 30 for someone who is allocated at 1.0 for the month of November (30 days) and 31 for the month of December (31 days). I just want it to be 1.0. See below for what I DON't want:

Here is a table of my mock data:

Employee Name	Project	FTE	Start Date	End Date
John	Project A	1.0	1-Nov-23	31-Jan-24
John	Project B	0.5	1-Mar-24	31-Dec-24
Jane	Project A	0.5	1-Nov-23	31-Mar-24
Jane	Project B	0.5	15-Feb-24	31-Jul-24
Jane	Project C	0.8	1-Jun-24	31-Dec-24
Bob	Project A	1.0	1-Nov-23	13-Dec-24
Bob	Project D	0.2	1-Jun-24	15-Aug-24

I have a separate Date table in Powerbi and have transformed the main table to show a row for every date between the Start and End dates for each of the data rows above.

Thanks in advance for the help!

Sheila

[danextian · Accepted solution]

Hi @scbjerre 

This seems to be what you're looking for.

Please see attached pbix  for details.

[scbjerre]

Thank you so much! this worked exactly as I needed it to.  Really appreciate the help and your expertise. 😀

[ryan_mayu]

what's the expected output based on your mock data?