Number of Employees at a particular Branch overtime

Hello,

I am trying to count the number of employees(distinct, as they tend to come back or go to another branch and still appear in the table under another branch) at a certain branch over different periods of time. I am attaching a sample below. I am trying to get the number of employees that are working at Branch “Doe”. My report will also be containing the year and quarter filter. And, I was wondering if somebody could help me figure out why my results are cumulative irrespective of the filter option applied to the quarter. This is probably pretty simple, I am a newbie when it comes to DAX and so any help would be appreciated.

Test_numbersenrolled.pbix (58.5 KB)

Hi @supergallagher25 welcome to the Forum!

There are a couple of things to point out here…

  1. The Model. You have two inactive relationships, which is required for the “events in Progress pattern”. But avoid bi-directional filtering (see (2)).
    image
  2. You don’t have a proper Date dimension table. The granularity of the Date dimension table should be at a daily granularity and consist of a complete date range (no duplicate OR missing dates!)
    image
  3. Your fact table has an empty value, my advise would be to fill those in with an arbitrary date, like the upper limit of your Date table for instance.
    image
    For Example

.

Number of Employees v2 = 
COUNTROWS(
    CALCULATETABLE( VALUES( tEmployee[EmpID] ),
        FILTER( VALUES( tEmployee[Enter Date] ), tEmployee[Enter Date] <= MAX( Dates[Date] )),
        FILTER( VALUES( tEmployee[Leave Date] ), tEmployee[Leave Date] >= MIN( Dates[Date] ))
    )
)

With this result
image

Here’s my sample file. I hope this is helpful.
eDNA - Numbersenrolled.pbix (135.2 KB)

1 Like

Hi @supergallagher25, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

1 Like

Thank you so much.