Forum Discussion

skasper's avatar
skasper
Icon for Responsive Resident rankResponsive 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.

     

rl_evans's avatar
rl_evans
Icon for Helper II rankHelper II
7 years ago

Anonymous - I have an active relationship between the date table and the employee table with a one to many relationship between date[date] and employee[hiredate].  

Each bar in the bar chart (using your example) represents a given period (i.e., date range).  This date range sets the date context for the DAX statement.  The date context filters the employee table to only those employees hired within the date range context (because of the relationship with the date table).  Think of it as if the DAX statement is getting executed separately for each period.  When looking for all active employees at the end of the period, all employees hired before the date range context have to be included as they may still be active employees.  The ALL() function allows the CalculateTable to ignore the period's date range context.

I tried using your DAX statement and found (in my model) that it doesn't include employees hired prior to the period.  Have you verified that your formula includes all requisitions opened prior to the period and are still open?

rl_evans's avatar
rl_evans
Icon for Helper II rankHelper II
7 years ago

 Anonymous - I did a little more digging.  Since you don't have an active relationship between date and requisition, then you don't have a date context that limits records in your calculate statement.  So, if you modified your DAX statement to look like the below, it should behave the same as mine.  This assumes the Last Modified date is blank if the requisition is still open.

Requisitions Open = 
VAR MaxDate =
    MAX ( 'Date Table'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT (Requisitions[Job Req ID] ),
        Requisitions[Approved Date] <= MaxDate,
        OR(ISBLANK(Requisitions[Last Modified]),
            Requisitions[Last Modified] > MaxDate)
    )