Forum Discussion

skasper's avatar
skasper
Responsive Resident
9 years ago
Solved

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). What I am trying to calculate i...
Need Help
  • skasper's avatar
    skasper
    9 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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
    )
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Ignore the above - just realised you said a date before the min date, not a blank date!