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.

     

rl_evans's avatar
rl_evans
Helper 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)
    )

 

Anonymous's avatar
Anonymous
Not applicable
7 years ago

rl_evans Thanks for digging into this - it's a trickier one to reoslve than I first thought!

I've tried the formula you suggested, but this still doesn't allow me to filter using the chart unfortunately.

I've also tried replicating what you have by removing my relationship with [Last Modified Date] and the [Date] in my Date Table, then making the relationship between [Approved Date] and [Date] active. I've then used your original formula. The chart looks fine, but when I seect a month in the chart to filter by, all my rows come up blank (see screenshot below).

Are you able to successfully filter for the relevant employees active in the period using you chart?