HR Dashboard

[tebtim19]

This is my sample measure I used for # of terminations

Terminations =

CALCULATE(COUNT(Employees[EmpID]),USERELATIONSHIP(Employees[TermDate],'Dates'[Date]),NOT(ISBLANK(Employees[TermDate])))

But the problem is my for all permanent employee Termdate is set to 5555.12.31

So it's not blank at all, can someone help me how to count those who leave in the company filtered by year.

I made another table for dates:

Dates =

VAR MinYear = YEAR ( MIN (Employees[HireDate] ) )

VAR MaxYear = YEAR ( MAX (Employees[TermDate]) )

RETURN

ADDCOLUMNS (

FILTER (

CALENDARAUTO( ),

AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

),

"Year", FORMAT([Date], "yyyy"),

--"Calendar Year", "CY " & YEAR ( [Date] ),

"Month Name", FORMAT ( [Date], "mmm" ),

"Month Number", MONTH ( [Date] ),

"Weekday", FORMAT ( [Date], "dddd" ),

"Weekday number", WEEKDAY( [Date] ),

"Year Month", FORMAT ([Date], "mmm yy"),

"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1

)

Not sure if the dates table that I added is correct.

I add also this date relationship to Hire date and TermDate

Anyone who have idea? 

Ref:  https://www.youtube.com/watch?v=dPxWZfAEofM

[v-yiruan-msft · Accepted solution]

Hi @tebtim19 ,

I created a sample pbix file(see attachment) for you, please check whether that is what you want. Please note that there is no relationship be created between Employees and dates table...

Terminations = 
CALCULATE (
    DISTINCTCOUNT ( Employees[EmpID] ),
    FILTER (
        'Employees',
        NOT ( ISBLANK ( Employees[TermDate] ) )
            && 'Employees'[TermDate] <= SELECTEDVALUE ( 'Dates'[Date] )
    )
)

Best Regards

[amitchandak]

@tebtim19 , Try measure like

Terminations =
CALCULATE(COUNT(Employees[EmpID]),USERELATIONSHIP(Employees[TermDate],'Dates'[Date]),Employees[TermDate] <date(5999,12,31))