Caclulate contracted hours at a dynamically specified date

[Miss]

I need to calulate the contracted hours per employee on a given date which can be dynamically selected through slicers and fitlers.

The base data I have been provided contains the employement history for the employees.

Based on this I need to perform the following actions:

- exclude any leavers - end date is before the specified date

- exclude any furture contracts - where effective date is after the specified date

- identify the contract with the latest date per each employee.

The data that has been supplied looks like the following:

Payroll Number	Effective Status	Effective Date	End Date	Career Change Reason	Hours Per Week
1	Current	02 January 2019		Starter	6.75
1	Current	27 April 2019		Change of Contract	12.5
2	Leaver	27 September 2018	29 March 2019	Starter	0
3	Current	29 January 2018		Starter	32.75
3	Current	04 May 2019		Promotion	32.75
4	Current	11 December 2017		Starter	36
5	Leaver	02 June 2018	30 March 2019	Internal Move	0
6	Current	14 March 2019		Starter	12.5
7	Leaver	06 September 2018	12 September 2018	Starter	0
8	Leaver	29 January 2018	14 April 2019	Starter	12.5
9	Current	29 January 2018		Starter	12.5
9	Current	20 April 2019			12.5
10	Current	17 June 2019		Starter	36
11	Leaver	29 January 2018	15 March 2019	Starter	26
12	Current	10 December 2018		Starter	16
12	Current	27 April 2019		Change of Contract	16
13	Current	29 January 2018		Starter	32.75
13	Current	30 March 2019		Demotion	32.75
14	Leaver	29 January 2018	06 July 2018	Starter	19.25
15	Leaver	24 March 2018	05 April 2018	Starter	26

Based on the specified date of the 27th July 2019 the total contract hours should calculate to be 191 hours (rows highlighted in red).

As I want to be able to dynamically calculate this I feel this needs to be caulcated using measures, rather than using the query editor.

I have managed to filter out leavers and future contracts based on the following measure:

   CALCULATE(

      sum(Contracts[Hours Per Week]),

      filter(Contracts, Contracts[End Date] >= min(PeakWeeks[TY]) || ISBLANK(Contracts[End Date])),

      FILTER(Contracts, Contracts[Effective Date] <=min(PeakWeeks[TY]))

   )

I know I need to select the max effective date per employee, but I'm struggling to identifiy the correct command to do this,

Is anyone able to help please?!

Thanks

[kentyler]

I hope I've understood your problem correctly.

Here is the measure for calculating the max effective date per employee:

End Date =

VAR current_employee = selectedvalue(EmployeeWork[Payroll Number])

RETURN CALCULATE(MAX(EmployeeWork[Effective Date]),ALL(EmployeeWork),EmployeeWork[Payroll Number] = current_employee)
It uses a VAR to store the Employee payroll number that is in the current filter context.

Then it uses CALCULATE to reset the filter context, first it gets ALL() the rows, then filters them for just the rows for the current employee, then it get the max effective date for that employee.

Using ALL() to restore rows that have been filtered out by the current filter context is a common trick when you need to do a count or sum or average over more rows than are in the current filter context. When a measure is being called for each row in a table only that row is in its filter context. If you take out the ALL() you will get the max date as the same effective date that is in the row.

[parry2k · Accepted solution]

@Miss hey do;;t have much time to think about it but try following two measures

Effective Hours per Employee = 
VAR __selectedDate = MAX ( 'Calendar'[Date] )
VAR __maxDate = 
CALCULATE ( 
    MAX( 'Table'[Effective Date] ),
    ALLEXCEPT( 'Table', 'Table'[Payroll Number] ), 
    'Table'[End Date] = BLANK() || 'Table'[End Date]>= __selectedDate, 
    'Table'[Effective Date] <= __selectedDate 
)
VAR __payroll = MAX ( 'Table'[Payroll Number] )
RETURN
CALCULATE( 
    SUM ( 'Table'[Hours Per Week] ), 
    'Table'[Effective Date] = __maxDate , 
    'Table'[Payroll Number] = __payroll  
) 

Effective Hours = SUMX(  VALUES ( 'Table'[Payroll Number] ), [Effective Hours per Employee] ) 

Output

Would appreciate Kudos 🙂 if my solution helped.

[Miss]

Thank you! that has worked perfectly! 

Bonus is I understand how you've acheived this answer!

[kentyler]

I hope I've understood your problem correctly.

Here is the measure for calculating the max effective date per employee:

End Date =

VAR current_employee = selectedvalue(EmployeeWork[Payroll Number])

RETURN CALCULATE(MAX(EmployeeWork[Effective Date]),ALL(EmployeeWork),EmployeeWork[Payroll Number] = current_employee)
It uses a VAR to store the Employee payroll number that is in the current filter context.

Then it uses CALCULATE to reset the filter context, first it gets ALL() the rows, then filters them for just the rows for the current employee, then it get the max effective date for that employee.

Using ALL() to restore rows that have been filtered out by the current filter context is a common trick when you need to do a count or sum or average over more rows than are in the current filter context. When a measure is being called for each row in a table only that row is in its filter context. If you take out the ALL() you will get the max date as the same effective date that is in the row.