Resource Utilization by year quarter and month

[AbhayB]

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

[Greg_Deckler]

@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)

[AbhayB]

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.