Forum Discussion
Employee New Hire
- 4 years ago
New Hires =
var minDate = MIN('Date'[Date])
var maxDate = MAX('Date'[Date])
return CALCULATE( COUNTROWS( 'Staff' ),
REMOVEFILTERS( 'Date'[Date])
'Staff'[HireDate] <= maxDate && 'Staff'[Hire Date] >= minDate
)Should work at any level of the date hierarchy
- 4 years ago
Hi Siddiq8686 ,
First you need to build a new "inactive" relationship like this
Your measures would beNew Hires = CALCULATE ( COUNTROWS ( VALUES ( Payroll_Merge_File[Employee #] ) ), USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] ) )Total Staff = VAR LastDateInFilter = CALCULATE ( MAX (Payroll_Merge_File[Hire Date] ), USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] ) ) VAR Result = CALCULATE ( COUNTROWS ( VALUES ( Payroll_Merge_File[Employee #] ) ), USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] ), Calendar_Table[Date] <= LastDateInFilter ) RETURN ResultYour report shall look like this
Please note that currently your Calendar Table starts from 2019 while hiring data starts from 1976. Therefore you need to update you Calendat Table to include all missing dates.
Hope this answers your query. Have a great day!
Hi Siddiq8686 ,
First you need to build a new "inactive" relationship like this
Your measures would be
New Hires =
CALCULATE (
COUNTROWS ( VALUES ( Payroll_Merge_File[Employee #] ) ),
USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] )
)Total Staff =
VAR LastDateInFilter =
CALCULATE (
MAX (Payroll_Merge_File[Hire Date] ),
USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] )
)
VAR Result =
CALCULATE (
COUNTROWS ( VALUES ( Payroll_Merge_File[Employee #] ) ),
USERELATIONSHIP ( Calendar_Table[Date],Payroll_Merge_File[Hire Date] ),
Calendar_Table[Date] <= LastDateInFilter
)
RETURN
ResultYour report shall look like this
Please note that currently your Calendar Table starts from 2019 while hiring data starts from 1976. Therefore you need to update you Calendat Table to include all missing dates.
Hope this answers your query. Have a great day!
Great This works. Thanks