Forum Discussion

hnam_2006's avatar
hnam_2006
Frequent Visitor
1 year ago

Calculate resource utilization

Hello all,

i need help to write measure which shows resource utilization by taking into consideration only working days of the resource. Main reason to do so is to see if resources are under utilized or over utilized.

i searched the forum but i did not see any answers which fits my requirement.

 

I have 4 tables:

- Date (list of all dates from 2023 to end of 2025)

- Holidays (list of holidays in 2023 and 2024)

- Resource (list of 4 resource and there working days)

- Resource tracking (project plan with planned allocation per task and actual usage)

 

I have split the planned and actual allocation hours per day using a measure written like below:

#HoursbyDay_Actual =
CALCULATE(
    SUMX(
        SUMMARIZE(
            FILTER(
                CROSSJOIN('Resource tracking','Date'),
                    'Date'[Date]>= 'Resource tracking'[Actual Start] && 'Date'[Date]<='Resource tracking'[Actual Finish]
            ),
            'Resource tracking'[RowNumber],'Date'[Date],'Resource tracking'[Actual allocation hrs],'Resource tracking'[Actual Start],'Resource tracking'[Actual Finish]
        ),
        DIVIDE(
            'Resource tracking'[Actual allocation hrs],
            NETWORKDAYS('Resource tracking'[Actual Start],'Resource tracking'[Actual Finish]))),
            'Date'[IsWorkingDay]=TRUE(),
            'Date'[calcIsHoliday]=FALSE()
            )
 
Problem 1: in above measure, i need to make sure the work is split per day only on the days the resource is working. (Eg: resource 1 is working monday to friday but resource 2 is working monday(2 hours), wednesday(4 hrs) and friday(3 hours). 
 
i have calculated total available hours as below:
TotalHoursAvailable =
CALCULATE(
    SUM('Date'[Working hours])*DISTINCTCOUNT('Resource tracking'[Assigned To]),
    'Date'[IsWorkingDay] = TRUE(),
    'Date'[DateWithSales]=TRUE()
)
 
Problem 2: Above measure is a workaround i have done to show a visual but i need a measure which sums the total available working hours of respective resource. I have created a seperate column for each resource like attached below.
Need Help

3 Replies

  • bhanu_gautam's avatar
    bhanu_gautam
    Super User
    1 year ago

    hnam_2006 , 

    you need to create measures that consider the working days and hours of each resource. Below are the steps and DAX measures to achieve this:

     

    Create a calculated column in the Date table to mark working days:
    IsWorkingDay =
    IF(
    'Date'[DayOfWeek] IN {1, 2, 3, 4, 5} &&
    NOT('Date'[Date] IN VALUES('Holidays'[Date])),
    TRUE(),
    FALSE()
    )


    Create a calculated column in the Date table to mark holidays:
    IsHoliday =
    IF(
    'Date'[Date] IN VALUES('Holidays'[Date]),
    TRUE(),
    FALSE()
    )


    Create a measure to calculate the actual hours by day, considering only working days:
    HoursbyDay_Actual =
    CALCULATE(
    SUMX(
    SUMMARIZE(
    FILTER(
    CROSSJOIN('Resource tracking', 'Date'),
    'Date'[Date] >= 'Resource tracking'


    Create a measure to calculate the total available working hours for each resource:
    TotalHoursAvailable =
    CALCULATE(
    SUM('Date'[Working hours]),
    'Date'[IsWorkingDay] = TRUE(),
    'Date'[IsHoliday] = FALSE()
    )


    Create a measure to calculate the resource utilization:
    ResourceUtilization =
    DIVIDE(
    [HoursbyDay_Actual],
    [TotalHoursAvailable]
    )


    These measures will help you calculate the resource utilization by considering only the working days and excluding holidays. You can adjust the working hours and days for each resource as needed.

  • hnam_2006's avatar
    hnam_2006
    Frequent Visitor
    1 year ago

    bhanu_gautam Thanks for the response but i still am facing same problems.

    You are suggesting i create seperate measures for each resource planned allocation, actual allocation and total available hours.

    In this case, i need to create a seperate page for all resources

    but i am looking also overall page and in this case, i am not sure how to get it to work

     

    below you can see, 

    Emily davis is working only monday and friday but hours planned and actual are splitting for all working days

     

    • hnam_2006's avatar
      hnam_2006
      Frequent Visitor
      1 year ago

      Could anyone throw light on this please? need to find a way for me to include , in calculation. only the working hours of a person and not the weekly working hours. A person works part time and another full time. How can i approch the solution here?
      Any help is appriciated 🙂 thanks