Forum Discussion
Active Employees per Period
Hi,
struggling with what should be simple. I have two tables ('crm_employees' and 'DimDates'). There exists a relationship between them (see picture below).
Table Relationship
What I am trying to calculate is
- the running total of people having started in a certain period, and
- the running total of still active people (i.e. 'Contract End Date' empty or in the future)
This is my Measure for the active ones:
Active Employees = CALCULATE( COUNTA(crm_employees[Employee]); crm_employees[Status Code] = "Active" )
And this for the running total:
Active Employees running total in Date =
CALCULATE(
'Key Measures'[Active Employees];
FILTER(
ALLSELECTED('DimDates'[Date]);
ISONORAFTER('DimDates'[Date]; MAX('DimDates'[Date]); DESC)
)
)My problems:
- is that it does not count employees where the contract start date is empty. I suppose that's because it cannot relate the empty 'contract start' with the 'DimDates' Date field.
- the total is only increasing, which is not correct. e.g. we had more employees in august 2016 than in june 2017, but this is not reflected as you can see below. The measure obviously applies the 'active' filter regardless of when an employee became inactive ('contract end date'). I think I need to redefine the measure for counting 'active' employees to consider the period during which they were active (from 'contract start date' to 'contract end date'), but don't know how to go about it.
Total Employees per Period
Thank you for the advice. I tried it and do not get the correct result, either. What I do get is the number of employees, who left in a specific period (e.g. month).
As I have to deliver the report today, I resolved to take a different approach, showing the number of joiners, leavers and overall evolution of active personnel as in the chart below.
31 Replies
skasper,
Please use the DAX below to calculate Active Employees and check if you get expected result.Active Employees = CALCULATE( COUNTA('crm_employees'[Employee]); FILTER('crm_employees' , ('crm_employees'[Contract Start] <= LASTDATE('DimDates'[Date]) && 'crm_employees'[Contract End]>= FIRSTDATE('DimDates'[Date]))) )
Regards,Thank you for the advice. I tried it and do not get the correct result, either. What I do get is the number of employees, who left in a specific period (e.g. month).
As I have to deliver the report today, I resolved to take a different approach, showing the number of joiners, leavers and overall evolution of active personnel as in the chart below.
I have the excat same probles.
please help
I don't think you can use the Active Status here as that one probably only works for the current Employees as of today right?
If you want historic "Active Employees" in a past month they need to have >= Contract Start and <= Contract End within that month.
Thank you. Yes, I think so, too and tried to do so. But so far without success. Any proposal, how the DAX would look like for this, would be much appreciated.
Not sure how this can be solved just by DAX.
I suggest you do a second Date Table joined on Contract End then you have Startdate and Enddate and then you can filter EndMonth <=
Hi,
I was trying to do something similar, but for job requisitions. Wanting to count the number of open requisitions over time. My "Start date" equivalent is "Approved date" and my "End date" equivalent is "Last modified date".
The below formula worked for me:
Requisitions Open = VAR MinDate = MIN ( 'Date Table'[Date] ) VAR MaxDate = MAX ( 'Date Table'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT (Requisitions[Job Req ID] ), Requisitions[Approved Date] <= MinDate, Requisitions[Last Modified] >= MaxDate )This is my output:
I have a simple data model:
Happy to provide more detail if that helps.
Thanks,
Matt
mtomlinson - The difference with your formula is that it won't include the requisitions that opened prior to the minimun date and are still open. This may or may not be relevant in your solution. When counting all active employees within a specified date range, those with a hired date before the date range (and are still active) need to be included.
rl_evans it's a good point. In my case, all requisitions have an approved date, and my date table is dynamically defined using the minimum approved date.
Would a simple OR clause not solve this though? Eg...
Requisitions Open = VAR MinDate = MIN ( 'Date Table'[Date] ) VAR MaxDate = MAX ( 'Date Table'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT (Requisitions[Job Req ID] ), OR(Requisitions[Approved Date] <= MinDate,ISBLANK(Requisitions[Approved Date])), Requisitions[Last Modified] >= MaxDate )
I have table contains data col's of EmpID, Shift Start Time, Shift End Time & Resonable Notice Time in case of closing business before business hours. I'm trying to filter Employees whose ShiftStartTime is less than 2 hours of Resonable Notice Time of Closing so they get compensation as per the policy. My syntax does not fit to filter those employees. Please advise.
I also tried calculating time difference by using DATEDIFF as
Step 1
Time Diff = DATEDIFF('Table1'[ShiftStartTime],'Table1'[Resonable Notice Time],MINUTE)Step 2Trying to filter only those EmpID's who has less than 120 mins and >= 0 as they many not have had chance to clock in. but not getting right syntaxEmpID's for Compensation = LOOKUPVALUE('Table1'[iEmpID],('Table1'[Time Diff]<120 &&'Table1'[Time Diff]>=0),BLANK())HI,
Can any body help me what is wrong in this dax to get final outpu. Before dax i will explain my requirement which is simple but unable to get the result.
1. i want to find find active employees, under the cadre of manager who are active.i have written dax as below.. Please correct me i was wrong.
Active persons =var currentdate =MAX('CALENDAR DATE'[Date])returnCALCULATE(COUNTROWS(EMPLOYEE_D),FILTER(EMPLOYEE_D,(EMPLOYEE_D[DATE_OF_JOINING] <= currentdate&& EMPLOYEE_D[DATE_OF_RELIEVING] >= currentdate), ALLSELECTED( EMPLOYEE_D[ACTIVE_FLG] = "Y", EMPLOYEE_D[DESIGNATION] in {"sales Manager", "sr.sales Manager"})))
