Number of Employees at a particular Branch overtime

DAX DAX Calculations
2

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
    image
    image849×283 14.8 KB

.

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