Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Power BI DAX : Resource Allocation Measures

Hi All, I am working on a Project Management dashboard, where I need to create a matrix of Resource Allocation on a particular assignment (month on month). Basically, how much a resource is occupied on a task for a particular month. 

 

Below is my input data : 

 

Based on Estimated Hours and Start Date, End Date, I need to calculate Allocation % of every resource for each task he/she is working on.

Key Pointers : 

1. An Employee is expected to work 8 hours a day. 5 days a week. So for Jul-21, let's say there are total 20 working days, so total working hours = 20*8 -> 160 Hrs.

2. A task can span over months, but allocation need to be calculated for every month

3. I have a Date table in place with weekday and weekend marked.

 

Below is expected output  

 

Explanation : 

1. Sahil is working on Task 1, spanning 1 July to 31 July, working 4 hours every day (80 hrs /20 days) hence 50% allocated for Jul-21.

2. Sahil's rest of the time is going in Task 2, spanning July and August, he is spending 4 hours every day in July and then once Task    1 gets over, will spend whole 8 hours a day in August. Hence, for Task 2, July Allocation is 50% but August Allocation % is 100%.

3. Same way for Alex, out of his 160 working hours for July, he will spend 100 hours on Task 3 making it 62.5% Allocated. (100/160).

4. Alex spending his remaining 60 hours of July on Task 4 thus making Allocation of 37.5%. For August, he will finish pending Task 4 of 40 hours(100-60) making it 25% Allocated.

 

Please help me, I am not able to write measures for it and it's a time critical task amitchandak Greg_Deckler Anonymous 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous ,

     

    According to your description, you want to calculate the % of actually working hours and expected working hours,right?

     

    First, you could use the following formula to get expected working hours(8h each day ) of each month:

    WorkHours =
VAR workdays =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            [Date] <= MAX ( 'Table'[EndDate] )
                && [Date] >= MAX ( 'Table'[Start Date] )
                && [WorkDay or Weekend] = "WorkDay"
        )
    )
RETURN
    workdays * 8

    In my data sample, it will return workingdays =22 ,which is different from yours. So I will just use the fixed value :

    var workhours=20 *8

    Then please try:

    MaxMonth =
VAR _start =
    MONTH ( MAX ( 'Table'[Start Date] ) )
VAR _end =
    MONTH ( MAX ( 'Table'[EndDate] ) )
RETURN
    IF ( _start = _end, _start, _end )

    July Flag=
VAR workhours = 20 * 8
VAR previoushours =
    CALCULATE (
        MAX ( 'Table'[Estmate (Hrs)] ),
        FILTER (
            ALL ( 'Table' ),
            [MaxMonth]
                = MAXX (
                    FILTER ( 'Table', 'Table'[Assignee] = MAX ( 'Table'[Assignee] ) ),
                    [MaxMonth]
                ) - 1
                && 'Table'[Assignee] = MAX ( 'Table'[Assignee] )
        )
    )
RETURN
    IF (
        MONTH ( MAX ( 'Table'[Start Date] ) ) = 7
            && MONTH ( MAX ( 'Table'[EndDate] ) ) = 7,
        MAX ( 'Table'[Estmate (Hrs)] ) / workhours,
        ( workhours - previoushours ) / workhours
    )

    Jul-21 = FORMAT([July Flag], "##.0%") &" Allocation"
    Aug-21 =
VAR workhours = 20 * 8
VAR percentage =
    (
        MAX ( 'Table'[Estmate (Hrs)] ) - [July Flag] * workhours
    ) / workhours
RETURN
    IF ( [MaxMonth] = 8, FORMAT ( percentage, "##.0%" ) & " Allocation" )

    Here is the final output:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous ,

     

    According to your description, you want to calculate the % of actually working hours and expected working hours,right?

     

    First, you could use the following formula to get expected working hours(8h each day ) of each month:

    WorkHours =
VAR workdays =
    CALCULATE (
        COUNTROWS ( 'Date' ),
        FILTER (
            'Date',
            [Date] <= MAX ( 'Table'[EndDate] )
                && [Date] >= MAX ( 'Table'[Start Date] )
                && [WorkDay or Weekend] = "WorkDay"
        )
    )
RETURN
    workdays * 8

    In my data sample, it will return workingdays =22 ,which is different from yours. So I will just use the fixed value :

    var workhours=20 *8

    Then please try:

    MaxMonth =
VAR _start =
    MONTH ( MAX ( 'Table'[Start Date] ) )
VAR _end =
    MONTH ( MAX ( 'Table'[EndDate] ) )
RETURN
    IF ( _start = _end, _start, _end )

    July Flag=
VAR workhours = 20 * 8
VAR previoushours =
    CALCULATE (
        MAX ( 'Table'[Estmate (Hrs)] ),
        FILTER (
            ALL ( 'Table' ),
            [MaxMonth]
                = MAXX (
                    FILTER ( 'Table', 'Table'[Assignee] = MAX ( 'Table'[Assignee] ) ),
                    [MaxMonth]
                ) - 1
                && 'Table'[Assignee] = MAX ( 'Table'[Assignee] )
        )
    )
RETURN
    IF (
        MONTH ( MAX ( 'Table'[Start Date] ) ) = 7
            && MONTH ( MAX ( 'Table'[EndDate] ) ) = 7,
        MAX ( 'Table'[Estmate (Hrs)] ) / workhours,
        ( workhours - previoushours ) / workhours
    )

    Jul-21 = FORMAT([July Flag], "##.0%") &" Allocation"
    Aug-21 =
VAR workhours = 20 * 8
VAR percentage =
    (
        MAX ( 'Table'[Estmate (Hrs)] ) - [July Flag] * workhours
    ) / workhours
RETURN
    IF ( [MaxMonth] = 8, FORMAT ( percentage, "##.0%" ) & " Allocation" )

    Here is the final output:

    Best Regards,
    Eyelyn Qin
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.