Need help in DAX

[jkhan]

Hi All,

I am trying to do sum of number of employees based on Date Filter also need to consider employees Termination Date while doing sum.

I have written below dax to do cumulative total but its not showing correct output. 

ISTERMINATED2 = IF (ISBLANK (Sheet1[terminated_Date])=TRUE(),1,
IF ((Sheet1[terminated_Date]) <= SELECTEDVALUE('date'[Date]),0,1)
)

TOTAL_ACTIVE2 = CALCULATE(COUNTA(Sheet1[PRN]) , Sheet1[ISTERMINATED2]=1)

TOTAL_RUNNING2 =
CALCULATE(
[TOTAL_ACTIVE2],
FILTER(
ALLSELECTED('date'[Date]),
ISONORAFTER('date'[Date], MAX('date'[Date]), DESC)
)
)
)

Sample Data : To download Excel I am using to do test. 

For Example Till 30-May-2021 I have 40 employees this is showing correct. 

In June 2021 month I have 3 termination but output still showing 40 It suppose to show 37. 

Please help to fix this issue. 


Thanks & Best Regards

Jamsher

[jkhan]

Hi @amitchandak 

Thanks for reference. 

I modified DAX and gettting expected results.  Please it will be great help if you can explain the little bit about below dax and also CROSSFILTER what is purpose of it. 

Current Employees = CALCULATE(
COUNTX(
FILTER
(Sheet1, Sheet1[hire_date]<=max('Date'[Date]) && (ISBLANK(Sheet1[terminated_Date]) || Sheet1[terminated_Date]>max('Date'[Date])))
,(Sheet1[PRN ])
)
,CROSSFILTER(Sheet1[hire_date],'Date'[Date],None)
)

[jkhan]

Hi All,

Please any suggestions. 

Thanks & Regards

Jamsher

[amitchandak · Accepted solution]

@jkhan , do you have both start date and termination date ?

Please refer to my blog on a similar topic if that can help. I think you need  join with termination date too

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[jkhan]

Hi Mr. Amit,

Thanks for reply. I have two Date field in employee master Table 1) Hire_Date 2) Termination_Date

I need to make sum of number of employees based on Date Filter and also need to substract the employee if there Termination Date fall before the selected date. 

Sure I will refer the blog and will udpate accordingly. 

Thanks & Regards

Jamsher