Forum Discussion
Helper IIINeed help in DAX
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 , 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
4 Replies
jkhanHi All,
Please any suggestions.
Thanks & Regards
Jamsher
- amitchandak
Super User
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
- 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
- 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)
)