How to show Staff Capacity against Work by Month

[SteveMForm]

Trying to calculate staff capacity to do work.

I have a Fact table ('Work Tracker') where each row  = Work Item, with each item having a start and end date and up to 7 resources with the capacity required for each (held as a decimal where Full Time Equivilant = 1.0) e.g. Resource Name 1, Capacity 1, Resource Name 2, Capacity 2 etc.

We have an organisation 'dimDates' table which looks extensive enough, and I have inactive 1..* relationship from the dimDates table to the Start and End dates in the 'Work Tracker' Fact table.

I know I'll need a measure(s?), but can't work out the DAX and am stuck.

I need to be able to sum up the total capacity across all work and show a month by month view on a line and stacked column chart (my line comes from another table which is working which shows the average availability).

I also need to be able to order the months correctly, ideally the start month on the visual = first month out of data from Start Dates and the last month out of the data from End Dates, handling any blanks where no Capacity recorded for a given month, and showing the year below the months.

The Y axis 'FTE Count' is the sum of Capacity for a given month

The report will need to be able to handle month start and end, and where the Start Date and End Dates are not the beginning or end of the month.

The stack is to show by Deputy Director column for each month

An additional bonus would be to then see by individual Resource Name, although this could be on a separate report page.

'Work Tracker' table below:

Work Ref

Title of Work

Description of Work

Request Date

Requestor

Group

Directorate or Team

Business Sponsor

Start Date

End Date

Request Type

Work Type

Service 1

Service 2

Service 3

Service 4

Service 5

Status

RAG

Aligned to Strategy?

Clear Deliverable?

Clear Value?

Priority?

Impact?

Risk?

Beneficiary?

Opprotunity?

Size Score

Complexity Score

Other Areas Involved

Priority Type

Deliverable

Deputy Director

Resource 1 name

Resource 1 Capacity

Resource 2 name

Resource 2 Capacity

Resource 3 name

Resource 3 Capacity

Resource 4 name

Resource 4 Capacity

Resource 5 name

Resource 5 Capacity

Resource 6 name

Resource 6 Capacity

Resource 7 name

Resource 7 Capacity

23-0031

Work 1

get stuff done

01/12/2023

fred

yellow

red

steve

02/10/2023

01/05/2024

External

Actual

FALSE

FALSE

FALSE

FALSE

FALSE

In Progress

Green

FALSE

TRUE

TRUE

TRUE

FALSE

TRUE

TRUE

TRUE

2

2

Tech/ platform

Doing important stuff

Other

Fred

Olga

0.70

Petra

0.20

23-0032

Work 2

get more stuff done

01/12/2023

fred

yellow

red

steve

01/12/2023

01/05/2024

External

Actual

TRUE

FALSE

FALSE

TRUE

TRUE

In Progress

Green

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

TRUE

2

4

None

More important stuff

Slides

Bert

Petra

0.70

Olga

0.20

23-0045

Work 3

re-do stuff

01/12/2023

fred

yellow

red

steve

02/11/2023

30/07/2024

Internal

Actual

FALSE

TRUE

FALSE

FALSE

FALSE

Blocked

Amber

FALSE

TRUE

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

1

2

None

Not aligned to priority

Report

Ernie

Peter

0.40

Bob

0.40

James

0.20

Petra

0.10

23-0047

Work 5

discuss results

01/12/2023

fred

yellow

red

steve

31/08/2023

31/10/2024

Internal

Actual

FALSE

TRUE

FALSE

FALSE

FALSE

Blocked

Amber

FALSE

TRUE

TRUE

FALSE

FALSE

FALSE

FALSE

TRUE

2

2

None

Not aligned to priority

Report

Grover

Lulu

0.30

Elton

0.20

23-0049

Work 7

get stuff done again

01/12/2023

fred

yellow

red

steve

31/08/2023

01/06/2024

Internal

Actual

FALSE

FALSE

FALSE

FALSE

FALSE

Blocked

Amber

FALSE

TRUE

TRUE

FALSE

FALSE

FALSE

FALSE

FALSE

2

2

None

Not aligned to priority

Model

Jimmy

Jim

0.20

Lulu

0.30

Thanks

[SteveMForm]

Hi @lbendlin or anyone else- could really do with some help on this one as deadline approaching!

Thanks in advance

[lbendlin]

Ive simplified the work tracker table

hahaha, good one.  

Sorry, I meant to say - working on a generic query to bring it into usable format.  Groupwise unpivoting is not that trivial in Power Query.

Note that you have a spelling difference between Resource 1 Name and Resource x name.  Power Query is case sensitive.

[SteveMForm]

Hi @lbendlin - thanks for replying.

Sorry if the 'simplified' work tracker over complicated still! The key columns in that table are really:

Title of Work, all the date fields, all the 'Resource n name' fields and all the 'Resource n Capacity' fields.

Note that you have a spelling difference between Resource 1 Name and Resource x name.  Power Query is case sensitive.

I'm being slow (I blame Friday feeling) - but didn't see where the spelling difference is.

Those table column headers should all read 'Resource n name'.

Thanks for your efforts- much appreciated 🙂

[lbendlin]

see attached

[SteveMForm]

Hi @lbendlin - first of all- that looks awesome as I think it's the additional view I was looking for!

First pass- I needed the stacks to be the 'Deputy Director' (seems like a minor change?)

WIth the worked.pbix file- I assume I'll be able to work out what to do to my real data set(s)/ tables and the DAX to replicate?

Thanks again for your time

[lbendlin]

Yes, just switch the legend

You can swap in your own data, and modify the calendar to include holidays and local weekends etc.

[lbendlin]

Phew.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("jdNbb4IwGAbgv9JwresBEL01cUvMku1uF8aLTj5ZM6BLW5b579eC5WjExNAPYngfXsrhELBwSUhIg0XwIdU3ckMGBmlTnc8olSXYCwxTghlhoZ0pjt0Y2fFZQWqXtzzjdiFPiT2+g1HNGbPHPS9At2e3f8eFRzCPYFdEIRUMJRRTdkOyBWUG4Unf5W63lZ/1HM47otg73J8VLFPZIJomqM8PCSaJF+xUKaAhgKqTol5qNOmio9K7lpW3uFvAHy9ECUiBrnKjxx6KSdcIv4KGsfM1jAGJB7hWUqFPldY9gAtd9wTNRnGCFyV/6ypeq7xqQ3e5keXMjhgT1p7gytDCIFneE7BJB11oPGjk8RY2npBMvg/EMy7KMYTilWfsRVFcmrV98EEnDxFi4gmujtOXEtoUXKMfrsxlUoPdmtHoM/Wvnvbjo7n44z8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Work Ref" = _t, #"Title of Work" = _t, #"Description of Work" = _t, #"Start Date" = _t, #"End Date" = _t, #"Deputy Director" = _t, #"Resource 1 Name" = _t, #"Resource 1 Capacity" = _t, #"Resource 2 name" = _t, #"Resource 2 Capacity" = _t, #"Resource 3 name" = _t, #"Resource 3 Capacity" = _t, #"Resource 4 name" = _t, #"Resource 4 Capacity" = _t, #"Resource 5 name" = _t, #"Resource 5 Capacity" = _t, #"Resource 6 name" = _t, #"Resource 6 Capacity" = _t, #"Resource 7 name" = _t, #"Resource 7 Capacity" = _t]),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Work Ref", "Title of Work", "Description of Work", "Start Date", "End Date", "Deputy Director"}, "Attribute", "Value"),
    #"Filtered Rows" = Table.SelectRows(#"Unpivoted Other Columns", each ([Value] <> "")),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Filtered Rows", "Attribute", Splitter.SplitTextByDelimiter(" ", QuoteStyle.Csv), {"Attribute.1", "Number", "Property"}),
    Removed = Table.SelectColumns(#"Split Column by Delimiter",{"Work Ref", "Title of Work", "Description of Work", "Start Date", "End Date", "Deputy Director", "Number", "Property", "Value"}),
    #"Capitalized Each Word" = Table.TransformColumns(Removed,{{"Property", Text.Proper, type text}}),
    #"Pivoted Column" = Table.Pivot(#"Capitalized Each Word", List.Distinct(#"Capitalized Each Word"[Property]), "Property", "Value")
in
    #"Pivoted Column"

[SteveMForm]

Hi @lbendlin 

Ok, so I've looked at the worked.pbix and have a few queries (don't hate):

1. The 'Worked' Y axis needs to be 'FTE' - a sum of all the capacity from the 'Resource 1 (etc) Capacity figures. I dont see what the Y axis figures relate to as an individual will never be more than 1.0 at any given time. 

2. Does the 'Worked' in your pbix = worked days? If so, it needs to be capacity used (by month). First pass visual to be stacked by Deputy Director, then the individual Reource one is an excellent additional report visual I was looking for in my original post

3. I see no relationships between your calendar table and Fact table- is this correct? I have inactive relationships between my calendar and Fact table

4. The code in your message (number 8 in post) is insane and I wouldnt know what to do with it or what within it I would need (if necessary) to alter to format my table correctly. I assume the is Power Query?

Thanks again

3. I 

[lbendlin]

1. confirmed. You can have multiple individuals on the same project.  FTE is per day, so in a month you see higher sums.

2. same as 1.  You can only have one legend field.

3. Yes, needs to be disconnected or inactive relationship

4. How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the Source step with your own source.

[SteveMForm]

Hi @lbendlin 

thanks for your support.

1. So on FTE, a given month should never exceed the total FTE of the team. WIth 37 staff, this shouldnt go above 37 for a given month.

With the sample (and reduced) data I gave you, those FTE figures for a given month should barely reach double figures. With is in mind, there maybe needs to be some adjustment to the calculation for a given month?

In my visual in original post, you can see the FTE Y axis only goes up to 40 as we have 37 staff. 37 Staff = 37 FTE at any given time. The lines show the adjustment (from another table which is working fine) which takes off the 'overheads'.

2. cool

3. cool

4. source = specific SharePoint list source (which is my source)?

Thanks

[lbendlin]

WIth 37 staff, this shouldnt go above 37 for a given month.

that is incorrect.  It shouldn't go above 37 for a given day.

[SteveMForm]

Hi @lbendlin - it's true on both counts- but I need to show it by month (working days in the month).

If you think about it- there's now difference- the capacity of all staff can't exceed what they can work- whether that's bt day, week, month or year- it should always be 37 (and if it goes above, the figures are wrong on input or they are overworked!

[SteveMForm]

@lbendlin that should say there is no difference not 'now' difference.

To illustrate:

Work 1

Start Date: 1/1/2024 End Date: 31/1/2024

Resource 1 Name: Fred, Resource 1 Capacity: 0.5

Resource 2 Name: Bill, Resource 2 Capacity: 0.25

Work 2

Start Date: 15/1/2024 End Date: 15/2/2024

Resource 1 Name: Fred, Resource 1 Capacity: 0.25

Resource 2 Name: Bill, Resource 2 Capacity: 0.5

On:

1/1/2024, total capacity being used = 0.75

15/1/2024, total capacity being used = 1.5

1/2/2024, total capacity being used = 0.75

Is it possible, that because Capacity being used can fluctuate on a daily basis (with lots of differing start and end dates anywhere in a given month), a stacked column chart isnt suitable to show by month?  (it would be possible by day) and a line would be better (that could still show by month)?

If we were looking to show what had been used in a given month (eg on the last (and single) day of every month we have a set of figures of what everyone has worked, that would be simpler, and could be done by stacked column? (not that I need that or am doing that)

Thanks

[SteveMForm]

Hi @lbendlin 

I thought of an analogy that may help with what the calculation needs to do.

Lets say we had a bucket that holds 10 litres.

The bucket has different amounts of water in it on any given day, and different people fill it, but never so it overflows.

If we want to show how much water was in the bucket over a period of time (a month) , we wouldnt add up how much was in the bucket on each day- (an average of 8 litres (not overflowing) would be 31 days x 8 litres = 248 litres if added all together- way over what its capacity is.

We need to record daily figure, but show on a scale by month- hence why a line graph may be better!

To show how much water was in the bucket (capacity used) on any given day

[lbendlin]

What you want to do is average the daily FTE sum over each month. That is a fallacy on so many levels. People can leave/join on any day of the month. The projects can start and end on any day of the month.  FTE assignments can change any day. Months have different numbers of days. Etc. Etc.

[SteveMForm]

No I don't want to average it- I was only saying that as part of the example rather than listing how much each individual put in the bucket on every single day. 

If (after all the different amounts held on each day put in by different people) we sum up what was in the bucket on every day, or what each individual put in on every day in the month, it is a different result than the one I am looking for. 

I am looking for what capacity is being used over a period of time at any one given time, not a sum total as I think you had worked out. I think this is why you had expressed it as worked (days?) time - equivilant to the 'how much water was put in over a period of time'.

[SteveMForm]

@lbendlin 

Just to add:

1. I appreciate you bearing with me on this one- I actually think we are very close to the solution- I know what I want, I just need to help you see what that is

2. I think that in your post 16/20, the rest of what you say was based on the thought that I wanted to average out results- which I dont, so hopefully it is redundant concern now. It is true to say that this is live data, and whilst reasonably stable, a Resource name or their Capacity may change occassionaly, and the End Date, and so that data would be updated at source. This would, I assume, just be automatically re-calculated in the report to update the view.

[lbendlin]

I know what I want, I just need to help you see what that is

I may not want to help you get that as I may not agree with the methodology.

[SteveMForm]

@lbendlin Oh, wow- that's an unexpected reply.

I really appreciate the help and time you have put into this, but it's not an issue of methodology.

There is a goal (to show capacity being used at a single point in time) and a method to get there, and that is the DAX/ Power BI.

This isn't an unusual scenario/ goal. 

Calculating capacity is common in workforce management. You look at what work you have on the books, how much resource it's taking and what you have spare to take on new work. That's what I am looking for as my goal.

I do suspect heavily that my original idea of a column visual by month may not work, and that a line visual is right. This is because the granularity the DAX needs to work at is by day, which a column visual by month wont work. Obv you could show a column visual by day, but it would be unsuable. The line visual could work shown by month, I think.

I'm reminded of a line visual here: Total Number Of Staff Over Time - Power BI Insights (youtube.com)

Bottom line is: adding up the capacity worked is a different goal to showing what capacity is being used at given time intervals.

I'd really appreciate your help to get this over the line as I think it's close, I don't think it's a disagreement about methodology, it's understanding the goal.

[lbendlin]

I think we agree that the computation needs to happen on day level.  Based on that you need to decide how to coagulate the monthly number.  It must include a weighting, for the reasons I mentioned (on/offboarding during the month, change in assignments during the month etc.  So a weighted average might work.

As for the line vs column - I think both will work. Line charts are usually reserved for cases where you have a temporal dependency (say, temperature over time).  If you squint a little then your FTE over time could be acceptably squeezed into a line chart. However, given you have department heads etc, a stacked column chart seems a better choice.