Active Heads each month, rolling 12 months

[BSwift]

I'm trying to find a measure which will give me the amount of Active Heads at the end of each month which can be used in a rolling 12 month graph.

The relevant fields in my "Headcount Test" table are candidate ID, start date and end date (and maybe Placement ID).

Candidates can have multiple placements active at the same time, which is where I think I'm struggling.

I have, at the moment, a measure which works when looking at a single month: 

Active Heads = CALCULATE(
DISTINCTCOUNT('Headcount Test'[CandidateID]),
FILTER(ALL('Headcount Test'),
'Headcount Test'[StartDate] < MAX(rd_DimDate[FullDate])+1),
FILTER(ALL('Headcount Test'),
'Headcount Test'[EndDate] >= MAX(rd_DimDate[FullDate])+1))

when I slice by any month I get the correct number, however I'm unable to use this in a graph across more than one month, I assume because of DISTINCTCOUNT on the Candidate ID.

If I change DISTINCTCOUNT to COUNT, I get a measure which I can use in a graph across many months, however the number is slightly off (by around 30ish) because it includes Candidates have more than one active placement at that time.

Our definition of Active Head comes from the Candidate ID not the Placement ID so,

Candidate1 having Placement1 and Placement2 counts as one head.

Am I missing something quite obvious? 

[Anonymous]

It's not clear to me what you *want* to return for multiple months.

The way your measure is now, is counts the distinct candidates ... that started before the end of current time window (could be a week, month or year...) and ended after the end of the current time window.

Because it sounds super similiar, I could google for "DAX events in progress" -- there are a bunch of good patterns for that.

[BSwift]

Apologies - 

I want, for each month, the number of active heads at the end of the month.

My logic was as you said: somewho starts before the end of the date range and ends after the end of the date range (where the date range is the end of a month). Therefore if both of those criteria are met then the candidate was active at the end of the month.

The issue comes with trying to get the total number for each month. I need to know how many were active at the end of Jan, Feb, Mar, Apr (and shown in a graph).

I can slice by one month at a time and get the correct value, however I can't put date on the X axis and see the end of month totals for each month with the measure I have at the moment.

I will see what that google search brings back, I have tried to find similar questions that have been answered but I haven't had much luck getting quite what I want

Thanks for the reply

[Anonymous]

Hrm.  If you can slice each month and get the correct value... that putting months on the axis of a chart... should *also* give you the correct values.  What is actually happening?

[BSwift]

Yes it's quite weird... I was leaning towards an issue with my filter because I have MAX(Date) it may only work when I slice by month i.e. setting a max date for that month. It will work with date on axis then but there is only one month showing, and when I unfilter the month it no longer works. 

When I try and and put the date on an axis without additionally filtering/slicing by a month it just loads forever "Working on it..." - however using Count over Distinctcount loads instantly so I think it's not that I'm lacking processing power

[Anonymous]

Oh, perf issue!?  Interesting.  How about in a table/matrix? 

[BSwift]

Anything where I'm trying to look at Active Heads by Month doesn't work at all with the DISTINCTCOUNT measure, all work with just count (but number incorrect... counitng all placement IDs not distinct candidate IDs)

[Anonymous]

I am guessing 'Headcount Test' is pretty big?

[BSwift]

I think around 20000 rows possibly more or less, I'm not 100% as I don't have access to it at this time. 

Would DISTINCTCOUNT vs COUNT make that much of a difference in processing if it were going to work though?

[Anonymous]

Ya, that isn't THAT interesting of a number of rows.  If were were talking 500k rows... sure.

DISTINCTCOUNT is certainly "harder" that COUNTROWS -- as DISTINCTCOUNT has to "keep track" of everything it has seen, where COUNTROWS is merely counting.

Your model too sensitive to share out?

[BSwift]

Should be able to provide it tomorrow when I have it available. 

For this purpose it's just Headcount Test and DimDate tables, I can provide pictures of one measure vs the other as well in the morning if they'll help

[BSwift · Accepted solution]

Nevermind... I've been extremely silly. 

I'm only unable to view when I don't select any month to slice by and must have assumed None = All.

When I slice by multiple months it worked, for some reason I didn't bother trying that in the first place.

Sorry for the time waste! and thanks for the replies.