Calculate correct total for a ratio

[Yggdrasill]

Hello.

I've been struggling with something that seemed to be rather easy but somehow it isn't.

I have 3 tables. One factTable with summarized hours workers register. I have one employee table (Resource) where I have the extra information on when they started and if they have quit then there's an end date as well.  I also have a calendar table. Relationship can seen on the picture.

For the measures it's pretty simple.

Hours = SUM('Mandatory Table'[Hours])
Mandatory hours can be found from the Calendar Table and Resource Table

Mandatory =
VAR _mandatoryHours = 7,5 //mandatory hours per day
//WorkRatio from Calendar is either 0 or 1 based on the day of the year
VAR _daycalc =
    CALCULATE (
        SUM ( Calendar[WorkRatio] ) * _mandatoryHours;
        FILTER ( 'Calendar'; Calendar[Date] <= 'Last Refreshed'[LastRefreshed] ) //filter future dates out
    )
        * AVERAGE ( Resource[Work Ratio%] ) //some resource are not 100% full time
VAR _calc = _daycalc * [Employee Count] //distinct count of resources
RETURN
        IF ( ISINSCOPE ( 'Calendar'[Date].[Year] ); _daycalc; _calc ) 

 Now for the ratio

Mandatory% =
VAR _calc1 =
    DIVIDE ( [Hours to Registered Service Lines]; [Mandatory]; 0 )
RETURN
    IF ( [Mandatory] = 0; BLANK (); _calc1 )

 This is what I get in return when I look at one resource and it seems to work

Now when I look at a resource that did quit (on the 17th) I get a wrong total

My question is, How can I leverage the result with DAX so that I can get a correct Total ? For this instance the mandatory hours should be 0 because the resource is no longer working.

I somehow need to check with the end date from the resource table and put that into account when I calculate mandatory hours.

Thanks in advance ! 

Ps. 'Mandatory Table' is a summarised factTable where I use DAX to summarize it. I'd like to be able to skip the physical creation of another table and just do the calculations within the vertipaq engine if that's possible.

[SQLbyoBI]

are we to assume these calculations will only ever be used in the context of a single employee, or should the calculation be able to handle scenarios where there are multiple employees in context?

If we assume a single employee, then this should help...

Mandatory =
VAR _mandatoryHours = 7.5 //mandatory hours per day

VAR __cur_resource_last_date = SELECTEDVALUE( Resource[EndDate] )

//WorkRatio from Calendar is either 0 or 1 based on the day of the year
VAR _daycalc =
    CALCULATE(
        SUM( Calendar[WorkRatio] ) * _mandatoryHours,
        FILTER(
            'Calendar',
            AND(
                'Calendar'[Date] <= 'Last Refreshed'[LastRefreshed],
                'Calendar'[Date] <= __cur_resource_last_date
            )
        )
    )
        * AVERAGE( Resource[Work Ratio%] ) //some resource are not 100% full time
VAR _calc = _daycalc * [Employee Count] //distinct count of resources
RETURN
    IF(
        ISINSCOPE( 'Calendar'[Date].[Year] ),
        _daycalc,
        _calc
    )
EVALUATE
 ( 'calendar' )

[Yggdrasill]

Thank you for the review!

However I have to be able to see one or multiple employees which are stores in the resource table.

[SQLbyoBI]

---

@Yggdrasill wrote:
Thank you for the review!

However I have to be able to see one or multiple employees which are stores in the resource table.

---

in that case, you can probably get away with something like this...

Mandatory STAGE =
VAR _mandatoryHours = 7.5 //mandatory hours per day

VAR __cur_resource_last_date = SELECTEDVALUE( Resource[EndDate] )

//WorkRatio from Calendar is either 0 or 1 based on the day of the year
VAR _daycalc =
    CALCULATE(
        SUM( Calendar[WorkRatio] ) * _mandatoryHours,
        FILTER(
            'Calendar',
            AND(
                'Calendar'[Date] <= 'Last Refreshed'[LastRefreshed],
                'Calendar'[Date] <= __cur_resource_last_date
            )
        )
    )
        * AVERAGE( Resource[Work Ratio%] ) //some resource are not 100% full time
VAR _calc = _daycalc * [Employee Count] //distinct count of resources
RETURN
    IF(
        ISINSCOPE( 'Calendar'[Date].[Year] ),
        _daycalc,
        _calc
    )
EVALUATE
 ( 'calendar' )

...then create another measure that references the one above at the resource level...

 Mandatory =  
    SUMX(
        /* or whatever the grain of the resource is called */
        VALUES( 'Resource'[ResourceID] ), 
        [Mandatory STAGE]
    )

This is a calculation needs to happen at the resource/day grain... so there's probably a better way to handle.

[Yggdrasill]

Thank you for your help. It got me started on this project again and I've solved all parts except for one minor problem.

I'll post my solution with .pbix demo file in the coming days for possible future help

[SQLbyoBI]

Great, glad it helped! Please do circle back around and share your final solution.