Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon'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.
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.
Regards,
Abhay
@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)
Thanks Greg for the response.
Here is the sample data -
Resource Name | Utilization | Month | Working_Days | Billable_Days | Resource_Count |
User1 | 71 | 2021-M11 | 21 | 15.00 | 1 |
User2 | 73 | 2021-M11 | 21 | 15.40 | 1 |
User3 | 72 | 2021-M11 | 21 | 15.13 | 1 |
User4 | 90 | 2021-M11 | 21 | 19.00 | 1 |
User1 | 61 | 2021-M12 | 23 | 14.00 | 1 |
User2 | 89 | 2021-M12 | 23 | 20.40 | 1 |
User3 | 87 | 2021-M12 | 23 | 20.00 | 1 |
User4 | 83 | 2021-M12 | 23 | 19.00 | 1 |
Based on the calculations shared initially, the expected utilization should be as per below -
User1 | 73 |
User2 | 77 |
User3 | 80 |
User4 | 82 |
2021-M11 | 76.50 |
2021-M12 | 80.00 |
2 Months | 78.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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
144 | |
74 | |
63 | |
51 | |
48 |
User | Count |
---|---|
211 | |
86 | |
64 | |
59 | |
56 |