How to calculate Utilization per month per resource

[ankitkalsara]

Hi team,

I have a requirment to calculate the utilization for each resource per month based on number of working days each month.

A resource can work on multiple tasks each month and the duration for each task is provided.

For each month, I need to find out number of days resource worked/number of working days in that month.

Attaching here pbix file as well.

Any help will be much appreciated.

[Ashish_Mathur · Accepted solution]

Hi,

Download my PBI file from here.

Hope this helps.

[ankitkalsara]

Hi @Ashish_Mathur , thank you so much for your reply.

I see in the pbix file, you created Many to one relationship created between Project Tasks and Calendar table. But I do understand how the Date field was created on the Project Tasks table?

I do not see any DAX as well for the Date field.

[Ashish_Mathur]

Hi,

Please refer to the steps in the Applied steps section of the Query Editor.

[ankitkalsara]

Hi @Ashish_Mathur : thank you, the solution did work.

I provided incorrect data model while posting the question, thus it is giving incorrect answer! (which was my mistake!)

The working days is getting calculated for all the months (despite of selecting the Project name, Task name in the slicer) and thus the Utilization(Ratio) is getting affected.

I created all the measured under Resource Assignments (3) and linked with the Calender table(5)

The model is from Microsoft Project Online.

Do let me know in case you have some suggestion on above problem.

Thank you again for your help.

[Ashish_Mathur]

Hi,

I cannot help unless i see your file.  Also show your expected result.

[ankitkalsara]

Hi @Ashish_Mathur,

Attached here is the file and it contains expected result.

[Ashish_Mathur]

This is very different from the question you posted earlier.  With so many tables and relationships there i am more confused now.

[ankitkalsara]

Hi @Ashish_Mathur ,

Sorry for the confusion. Attached is the file with minimum tables. 

The overall schema is as below

• Project(one) --> Project Tasks (Many)
• Project Tasks(one) --> Resource Assignments(many)
• Resource Assignments(many) --> Resources(one)

Resources are assigned to the Tasks on the Resource Assignment Table with Start Date and Finish date of a task.

we need to determine how many days the resource worked for each month

e.g. Resource assigned to task with date = 06/07/22 to 06/08/22 (2 days)

then overall he worked for 2/22(working days in June) 

and we need to find cumulative working for each resource.

I hope the new file will be clear.

[Ashish_Mathur]

Why have you not carried out the transformations that i have in the PBI file that i shared with you?  Please study that solution file carefully and replicate those steps in your file.

[ankitkalsara]

Hi @Ashish_Mathur ,

I have now recreated the file with minimum tables and all the transformations are created.

The measures are now appended with "NEW" in the field names.

[Ashish_Mathur]

Hi,

I dragged Year and Month in the table visual.  See the image

[ankitkalsara]

Thank you so much @Ashish_Mathur for your help 😊

[Ashish_Mathur]

You are welcome.

[mahenkj2]

Hi @ankitkalsara ,

I add sample file with some working.

For this purpose, you need to add a date table based on your need. I added one in DAX and considering Sunday as Holiday, but you need to add other offs as well. This you should be able to accomplish in DAX or Power query. There are many examples and ready to use codes available for this purpose. Please search and try to prepare a suitable data table.

Then relate this with your fact table. Now task should be not so difficult.

Then I added two measures, one is Total count of working days of the month and % utilization.

Total working does not match with your expectation, because date table did not consider all such holidays as may be in your case.

Please find link here:

https://drive.google.com/file/d/1ibXeW6PGKa1HR-2YmP08pQ6lgVzKZiUl/view?usp=sharing

Hope it helps.