calculate per month resource utilization in percentage based on start date and end date column

[KDPathak]

Hi Team,

I am struggling to calculate and how case per month resource utilization in % based on start date and end date column.

example:

resource |       start date   | end date

r1           | 02 January 2024 | 7 February 2024

r2           | 07 February2024    | 20February 2024

r2           | 24 April2024    | 23 June 2024

r3           | 03 July 2024     | 19 August 2024

r1           | 14 March 2024 | 28 April 2024

Example expected result in verticle bar chart:

r1 utilized in Jan 18% out of working days

r2 utilized in Q1 79%

Time filter:

Year, Quarter, Month

Any stepwise instructions or redirection to article will be much helpful.

Best Regards,

KD

[KDPathak]

@Ashish_Mathur , thanks for your quick reply. I will take some time to understand the DAX functions used in file.

Menwhile adding one more complexicity in this case.

1. only workings days to be considered for overall calculations.

2. if any resource had overlapping start and end date entries which are in between the larger date span, those needs to be ignored.

example,

resource |       start date   | end date

r1           | 02 January 2024 | 7 February 2024

r1           | 02 January 2024 | 04 January 2024 ------ this entry need to ignore in overall utilization calculation

[Ashish_Mathur]

Hi,

After the last step of Power Query, select all columns, right click and select Remove Duplicates.  Click on Close and Apply.  This will take care of point 2.  For point 1, you will have to create a Weekday column in the Calendar table and then tweak the CALCULATE() measure to exclude weekends.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[KDPathak]

Thanks for your quick turnaround!

I am trying to implement the suggested steps. While doing so, I am unable to understand the refernece value for 'IsWorkingDay'. can you help to get it?

'Calendar'[IsWorkingDay]

[suparnababu8]

 @KDPathak 

To calculate resource utilization per month based on start and end dates in Power BI, you can follow these steps:

Step-by-Step Instructions

1. Create a Calendar Table:

   • First, create a calendar table to cover the date range of your data.

Calendar = CALENDAR(MIN('YourTable'[StartDate]), MAX('YourTable'[EndDate]))
​

• Create a Relationship:

  • Create a relationship between your main table and the calendar table using the date columns.

• Calculate Working Days:

  • Calculate the number of working days in each month.

WorkingDays = 
CALCULATE(
    COUNTROWS('Calendar'),
    'Calendar'[IsWorkingDay] = TRUE()
)
​

• Calculate Days Worked by Each Resource:

  • Create a measure to calculate the number of days each resource worked in a given month.

DaysWorked = 
CALCULATE(
    COUNTROWS('Calendar'),
    FILTER(
        'Calendar',
        'Calendar'[Date] >= MIN('YourTable'[StartDate]) &&
        'Calendar'[Date] <= MAX('YourTable'[EndDate])
    )
)
​

• Calculate Utilization Percentage:

  • Create a measure to calculate the utilization percentage.

UtilizationPercentage = 
DIVIDE(
    [DaysWorked],
    [WorkingDays],
    0
)
​

• Example Visualization

  • Create a bar chart with Resource on the axis and UtilizationPercentage as the value.
  • Use slicers for Year, Quarter, and Month to filter the data as needed.

These steps should help you calculate and visualize resource utilization in Power BI.