Power BI DAX : Resource Allocation Measures

[sahildoshi]

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

[v-eqin-msft · Accepted solution]

Hi @sahildoshi ,

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.