Power BI DAX : Resource Allocation Measures

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.