Forum Discussion

ibrewop23's avatar
ibrewop23
Frequent Visitor
3 years ago

Calculating Employee Average Hours per Month (using Weekly Hours)

I am attempting to calculate the average number of working hours per month per employee using their weekly hours against their job. 

 

1 employee can have multiple jobs active at any time. Each job has a weekly hour value, a job start and end date. The assumption is made that an employee works 7 days a week each month of the year from the start to the end date of their job.

 

Example Data:

 

Employee 1 is not a problem. I can do the following (correct me if this is wrong):

VAR SOMcurrent = MIN ( 'DateTable'[Date] )
 
VAR EOMCurrent = MAX ( 'DateTable'[Date] )
 
VAR HoursPerWeek =
    CALCULATE (
        SUM ( 'Job'[Hours] ),
        FILTER (
            'Fact Job',
            'Fact Job'[StartDate] <= SOMcurrent 
                && 'Fact Job'[EndDate] >= EOMCurrent 
        )
    )
 
Hours Per Month = HoursPerWeek / 7 days * days in selected month

Employee 2 is tricker because their jobs don't start or end at the end of the month AND they're jobs have crossover, therefore their contract hours need summed and then averaged.
 
I have written a long dax statement using further calculates, date filtering by MONTH and YEAR. But I need to specifiy this to calculate at the employee level in the DAX. And I am a unsure as to how I can achieve this.
 
Thanks in advance.
Need Help