Forum Discussion
Active 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.
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 )Ignore the above - just realised you said a date before the min date, not a blank date!
rl_evans Actually, I don't see why that would matter - if an employee had a date before the minDate, it would still meet the criteria to be counted...
Am I missing something?
rl_evans also, as an aside - how have you defined your data model? Are you using inactive relationships? If so, have you be able to keep ineractions working so that you can, for example, select a particular month and view all employees active for that particular month?
