topic Re: How to show Staff Capacity against Work by Month in DAX Commands and Tips

How to show Staff Capacity against Work by Month

SteveMForm — Mon, 05 Feb 2024 11:33:36 GMT

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

In Progress

Green

FALSE

TRUE

FALSE

TRUE

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

TRUE

In Progress

Green

TRUE

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

Blocked

Amber

FALSE

TRUE

FALSE

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

Blocked

Amber

FALSE

TRUE

FALSE

TRUE

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

Blocked

Amber

FALSE

TRUE

FALSE

None

Not aligned to priority

Model

Jimmy

Jim

0.20

Lulu

0.30

Thanks

Re: How to show Staff Capacity against Work by Month

lbendlin — Mon, 05 Feb 2024 23:01:39 GMT

What's your capacity currency? Days? Hours? Minutes? Does your calendar table have a workday flag? What are the business hours? Timezones?

Re: How to show Staff Capacity against Work by Month

SteveMForm — Tue, 06 Feb 2024 09:46:04 GMT

Hi there

so capacity currency as stated is FTE (|Full Time Equivilant) where a Full Time employee is 1.0. We use FTE as it is both a way of determining headcount, but also in more granularity, capacity.

In this report, the data is captured under 'Resource 1 Capacity' and similar columns, and so how much capacity an item of work requires is captured by staff member(s) and expressed as the decimal figure e.g. 0.1, 0.5 etc.

The Y axis in the report expresses the total FTE, and the lines (form another table that is working fine) show the full FTE count (red line) and the maximum capacity (orange) we should not go beyond as we then impact on the time needed to do other responsibilities (line management, admin, learning etc).

The calendar table does seem to have a weekday column (values for what day of the week it is) and a 'WeekdayWeekend' column where values are one or the other depending obviously on the day of the week. There is also a 'IsBankHoliday' column for the public holidays.

The business hours aren't relevant I think, the timezone is GMT, but I also think its not relevant!

Re: How to show Staff Capacity against Work by Month

lbendlin — Tue, 06 Feb 2024 21:25:57 GMT

Unpivot or split the work tracker table to bring it into usable format and post a sample of the calendar table.

Re: How to show Staff Capacity against Work by Month

SteveMForm — Wed, 07 Feb 2024 09:22:55 GMT

Hi there

Ive simplified the work tracker table:

Work Ref

Title of Work

Description of Work

Start Date

End Date

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

02/10/2023

01/05/2024

Fred

Olga

0.70

Petra

0.20

James

0.20

23-0032

Work 2

get more stuff done

01/12/2023

01/05/2024

Bert

Petra

0.70

Olga

0.20

Bob

0.30

23-0045

Work 3

re-do stuff

02/11/2023

30/07/2024

Ernie

Peter

0.40

Bob

0.40

James

0.20

Petra

0.10

23-0046

Work 4

examine results

02/11/2023

31/05/2024

Barnie

James

0.20

Bob

0.30

23-0047

Work 5

discuss results

31/08/2023

31/10/2024

Grover

Lulu

0.30

Elton

0.20

23-0048

Work 6

sit on results

31/08/2023

31/12/2024

Barnie

Elton

0.50

James

0.30

23-0049

Work 7

get stuff done again

31/08/2023

01/06/2024

Jimmy

Jim

0.20

Lulu

0.30

23-0050

Work 8

christmas party

31/08/2023

30/04/2024

Fred

Bob

0.10

Lulu

0.40

Here is a sample of the calendar table:

Date

MonthNum

Month

MonthLong

Quarter

Year

FiscalMonthNum

FiscalMonth

FiscalMonthLong

FiscalQuarter

FiscalYear

CurMonthOffset

CurQuarterOffset

CurYearOffset

FutureDate

CurFiscalYearOffset

MonthYearNum

MonthYear

MonthYearLong

WeekdayNum

Weekday

WeekdayWeekend

WeekSequenceNum

CurWeekOffset

EndOfMonth

ReportingMonth

ReportingMonthName

ReportingMonthYear

ReportingMonthYearNum

EndofWeek

FiscalWeekNum

IsReportingDay

IsBankHoliday

FiscalBudgetVersion

FiscalForecastVersion

CurFiscalWeekOffset

ReportingMonthNum

ReportingMonthYearNum2

CurrentDayOffset

IsLeapYear

StartofMonth

DaysIntoFinancialYear

DayNum

CurReportingMonthOffset

Today Slicer

Last Full Month Slicer

CY+1

CY+2

CY+3

CY+4

CY+5

CY+6

FiscalYearNum

31-Dec-24

Dec

December

2024

Dec

December

FQ3

2024/2025

Future

202412

Dec-24

Tue

Weekday

875

31-Dec-24

Dec

Dec-24

202412

04-Jan-25

FALSE

B09

F10

-4

202409

341

TRUE

01-Dec-24

275

12/31/2024

Dec-24

2025/2026

2026/2027

2027/2028

2028/2029

2029/2030

2030/2031

4049

30-Dec-24

Dec

December

2024

Dec

December

FQ3

2024/2025

Future

202412

Dec-24

Mon

Weekday

875

31-Dec-24

Dec

Dec-24

202412

04-Jan-25

FALSE

B09

F10

-4

202409

340

TRUE

01-Dec-24

274

12/30/2024

Dec-24

2025/2026

2026/2027

2027/2028

2028/2029

2029/2030

2030/2031

4049

Re: How to show Staff Capacity against Work by Month

SteveMForm — Fri, 09 Feb 2024 09:39:59 GMT

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

Thanks in advance

Re: How to show Staff Capacity against Work by Month

lbendlin — Fri, 09 Feb 2024 17:40:23 GMT

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.

Re: How to show Staff Capacity against Work by Month

lbendlin — Fri, 09 Feb 2024 17:49:03 GMT

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"

Re: How to show Staff Capacity against Work by Month

SteveMForm — Fri, 09 Feb 2024 17:54:07 GMT

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 🙂

Re: How to show Staff Capacity against Work by Month

lbendlin — Fri, 09 Feb 2024 18:05:41 GMT

see attached

Re: How to show Staff Capacity against Work by Month

SteveMForm — Fri, 09 Feb 2024 18:13:57 GMT

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

Re: How to show Staff Capacity against Work by Month

lbendlin — Fri, 09 Feb 2024 18:26:56 GMT

Yes, just switch the legend

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

Re: How to show Staff Capacity against Work by Month

SteveMForm — Fri, 09 Feb 2024 18:32:50 GMT

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

Re: How to show Staff Capacity against Work by Month

lbendlin — Fri, 09 Feb 2024 18:39:14 GMT

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.

Re: How to show Staff Capacity against Work by Month

SteveMForm — Fri, 09 Feb 2024 18:53:10 GMT

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

Re: How to show Staff Capacity against Work by Month

lbendlin — Fri, 09 Feb 2024 20:11:54 GMT

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.

Re: How to show Staff Capacity against Work by Month

SteveMForm — Fri, 09 Feb 2024 20:35:54 GMT

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!

Re: How to show Staff Capacity against Work by Month

SteveMForm — Fri, 09 Feb 2024 21:59:08 GMT

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

Re: How to show Staff Capacity against Work by Month

SteveMForm — Fri, 09 Feb 2024 23:08:38 GMT

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

Re: How to show Staff Capacity against Work by Month

lbendlin — Fri, 09 Feb 2024 23:25:01 GMT

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.