topic Re: Need help in DAX in DAX Commands and Tips

Need help in DAX

jkhan — Wed, 28 Jul 2021 07:17:00 GMT

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

Re: Need help in DAX

jkhan — Wed, 28 Jul 2021 08:10:55 GMT

Hi All,

Please any suggestions.

Thanks & Regards

Jamsher

Re: Need help in DAX

amitchandak — Wed, 28 Jul 2021 08:16:01 GMT

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-trend/ba-p/882970

Re: Need help in DAX

jkhan — Wed, 28 Jul 2021 08:34:23 GMT

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

Re: Need help in DAX

jkhan — Wed, 28 Jul 2021 09:09:04 GMT

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)
)