cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Reply
BSwift
Frequent Visitor

Active Heads each month, rolling 12 months

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? 

 

 

 

 

 

 

1 ACCEPTED SOLUTION
BSwift
Frequent Visitor

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.

View solution in original post

11 REPLIES 11
Anonymous
Not applicable

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.

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
Not applicable

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?

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
Not applicable

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

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
Not applicable

I am guessing 'Headcount Test' is pretty big?

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
Not applicable

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?

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
Frequent Visitor

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.

Helpful resources

Announcements
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Dashboard in a day with date

Exclusive opportunity for Women!

Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors