DAX - Miscounting

[hmltnangel]

Hi all. I have tried a few different route for this one, and cant quite get it right. I am hopeful you kind people will fix this.

I have a Date/Calendar table created called "Date" with a column of dates. I have a table with all staff data contained in it, called StaffCounts. This contains columns for "Name", "Start Date", "End Date".

I want to be able to count how many people were employed on a specific date that I can select via filters, or Slicers.

The code I have used is as follows: (basically an adaptation of this - Open Tickets)

Staff on Date =
CALCULATE(
    COUNTROWS(staffcounts),
    FILTER(
        staffcounts,
        STAFFcounts[Current Employment: Start Date] <= MAX('Date'[Date]) &&
        (staffmaster[Current Employment: end date] >= MAX('Date'[Date]) || ISBLANK(staffcounts[Current Employment: end date]))
    )
)

Now what I think is happening, is that its counting how many people started on a filtered date, rather than the number of people employed on a set date. Any suggestions?

 

[hmltnangel]

A little crossposted one. (Not one for doing that but at least it worked this time)

I managed to resolve this with the use of the suggestion here:

PowerBI/DAX Calculating Incorrectly

I crossposted a similiar question on MrExcel - but no reply so asking here hoping theres an answer. May not be advanced for some of you, but to me it is :) I have an CalendarAuto() Date Table creat...

stackoverflow.com

And the code:

Employed Staff Count =
  var minD = MIN('Calendar'[Date])
  var maxD = MAX('Calendar'[Date])
  RETURN CALCULATE(
    DISTINCTCOUNT('STAFFMASTER'[Unique ID]),
    'STAFFMASTER'[Current Employment: Start Date] <= minD &&
    ( 'STAFFMASTER'[End Date] >= maxD || ISBLANK('STAFFMASTER'[End Date]) ),
    CROSSFILTER('Calendar'[Date], STAFFMASTER[Current Employment: Start Date], None)
  )

This works perfectly.