Count related table values

[JLip]

Hello,

Recently I've been working on HR dashboard and I am having a problem. I have 2 tables: 'Headcount' and 'Leavers' (related by ID number) + 'Calendar' table. 'Headcount' table contains all reports of subsequent months and is related to 'Calendar' by "Reporting Period". In 'Leavers' table I have "Leave Period" column. I need to count number of employees who left the company. It'd be easy if I could filter for people which Headcount[Reporting Period] = Leavers[Leave Period], problem is that if certain person's last day worked was at last day of month, we count that this employee left company in the following month. This results in fact that this person won't be appearing in 'Headcount' table anymore, so last reporting period will always be smaller than leave period and I can't find proper solution on my own here. How can I count number of leavers so the amount is both in compliance with Date filters and maintain all dependencies from Headcount table (for exmaple filtering visualizations by team, gender, manager name etc.). Currently I've tried adding relation between Calendar[Date] and Leavers[Leave Period], but as already mentioned I am losing all other filter dependencies besides correctly working date. Anyone could help me here?

[amitchandak]

@JLip , Try a measure like

Cumm Sales = CALCULATE(Count(Headcount[Emolyeee]),filter(allselected(date),date[date] <=max(date[Date]))) -CALCULATE(Count(leavers[Emolyeee]),filter(allselected(date),date[date] <=max(date[Date])))

[JLip · Accepted solution]

@amitchandak Thanks for your answer. This measure is not exactly what I've been looking for. 

Meanwhile I've came with my own solution. I've created calculated column in table 'Headcount'.

Leave Period =

IF(Headcount[Last Day Worked] = EOMONTH(Headcount[Last Day Worked],0),EOMONTH(Headcount[Last Day Worked],1),EOMONTH(Headcount[Last Day Worked],0))

And then used a measure:

Leavers =IF(ISBLANK(CALCULATE(DISTINCTCOUNT(Headcount[ROKID],USERELATIONSHIP('Calendar'[Date],

Headcount[Leave Period]))),0,CALCULATE(DISTINCTCOUNT(Headcount[ROKID]),USERELATIONSHIP('Calendar'[Date],Headcount[Leave Period])))

[amitchandak]

@JLip , Kudos to you. 2 from my side. 😀