cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Utilization of resources based on start/end date.

Hello, im trying to calculate utilization rate for resourcers in a company.

The base table looks like this:

 Customer Resource Start Date End Date Customer A Worker 1 02.01.2023 14.04.2023 Customer A Worker 2 02.01.2023 29.12.2023 Customer A Worker 3 16.01.2023 28.02.2023 Customer A Worker 4 01.03.2023 21.04.2023 Customer A Worker 4 05.05.2023 06.06.2023 Customer A Worker 5 01.03.2023 31.03.2023

I've added also column that is calculating working days between start and end date

I would like to calculate utilization for each Worker in a Matrix Visual.

In column I'd like to have year/month in rows there will be Customer/Workers hierarchy and of course values that will be represented as utilization rate so working days between start/end date divided by working days in that month.

I was trying to create something based on this https://www.youtube.com/watch?v=YL7H1Rqckb0 but I believe it should be slightly modified.

Currently Im receiving something like this:

and my formula looks like this:

formula =
VAR enddate =
MAX ( 'Calendar'[Date] )
VAR startdate =
MIN ( 'Calendar'[Date] )
VAR result =
CALCULATE (
SUM ( 'Data'[workingdays] ),
ALL ( 'Calendar' ),
'Data'[Start Date] <= enddate,
'Data'[End Date] > startdate
)
RETURN
result

5 REPLIES 5
Community Support

``````CountDay =
var _a = MIN('Calendar'[Date])
var _b = MAX('Calendar'[Date])
return SUMX('Table',IF(DATEDIFF(MAXX({_a,[Start Date]},[Value]),MINX({_b,[End Date]},[Value]),DAY)>=0,DATEDIFF(MAXX({_a,[Start Date]},[Value]),MINX({_b,[End Date]},[Value]),DAY)))

Rate =
DIVIDE([CountDay],SUMX('Table',DATEDIFF([Start Date],[End Date],DAY)))``````

Final output:

Best Regards,

Jianbo Li

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

Helper I

Woah 🙂 it loks great, is there any possibility to calculate it only for working days/including holidays? How should I modify the formula

Community Support

NETWORKDAYS returns the number of whole workdays between two dates (inclusive). Parameters specify which and how many days are weekend days. Weekend days and days specified as holidays are not considered as workdays. You can try to modify the DAX with NETWORKDAYS function.

Best Regards,

Jianbo Li

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

Super User

@rafal_walisko , hope the calendar is not connected with data

refer

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs

Power BI: HR Analytics - Employees as on Date : https://youtu.be/e6Y-l_JtCq4
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Helper I

Thanks for the links, I see that you are calculating for example count of projects. In my case I would like to present the number of working days for each person in each project monthly. That the actual problem. I was able to present the project count.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors