Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.

Reply
AbhayB
Frequent Visitor

Resource Utilization by year quarter and month

We have a model where projects, resource time sheets and a time period tables are recorded.

 

Projects table have Project Type and other fields.
Timecard table contains all the timesheets submitted by resources which includes billable and non-billable hours.
Time_period table contains the working days in a month which excludes holidays and weekends. e.g. 23 working days.

 

I am calculating Billable Days as per below measure -

 

Billable_Days =
( CALCULATE
( SUM('Timecard'[Non_Billable_Hours_Submitted__c]),
'Project'[Project Type] <> "Admin", 'Project'[Project Type] <> "Internal"
) / 7.5 +
(SUM('Timecard'[Billable_Hours_Submitted__c]) / 7.5)
)

 

And Utilization is calculated in another measure as -

 

Utilization =
(
( 'Timecard'[Billable_Days] /
( SUM('Time_Period'[Working_Days]) * DISTINCTCOUNT('Timecard'[Resource_Name])
)
) * 100
)

 

The utilization for each Resource is calcuating correctly as seen the screen shot, however, unable to get the average Utilization when Month/Quarter/Year is selected.


For e.g. Utilization for Nov 2021 should be (71+73+72+90)/4 = 76.5 where 4 is distinct count of resource.
For Dec 2021 it should be (61+89+87+83)/4 = 80 where 4 is distinct count of resource.


The average utilization for both months should be (76.5+80)/2 = 78.25 ~ 78

 

Requirement to show the utilization based on Date Slicer selection which can be year/quarter/month.

 

I've tried suggestions form other threads, but issue isn't resolved.

 

Any suggesstions are much appreciated.

 

AbhayB_0-1645097654889.png

 

Regards,

Abhay

 

2 REPLIES 2
Greg_Deckler
Super User
Super User

@AbhayB Would need sample data posted as text. That said, my book Learn Power BI has Utilization as the central use case and I also included Utilization in DAX Cookbook Recipe 1 of Chapter 8. Chapter 8 is all about project metrics:

PacktPublishing/DAX-Cookbook: DAX Cookbook, Published by Packt (github.com)



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Thanks Greg for the response.

 

Here is the sample data -

Resource NameUtilizationMonthWorking_DaysBillable_DaysResource_Count
User1712021-M112115.001
User2732021-M112115.401
User3722021-M112115.131
User4902021-M112119.001
User1612021-M122314.001
User2892021-M122320.401
User3872021-M122320.001
User4832021-M122319.001

 

Based on the calculations shared initially, the expected utilization should be as per below -

 

User173
User277
User380
User482

 

2021-M1176.50
2021-M12

80.00

 

2 Months78.25

 

This is an example for 2 month period, expected to get the results based on Month Slicer selection and it could a year / quarter / month.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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