Forum Discussion
Calculate HR trend graph
Simon,
Good Point about replacing the countrows function here.
For the year HC, I have some consideration here. Let's say one person who joins at 2001.1.1 and leaves at 2001.1.31, when under month range, we will count this one as a head count, right? But when the time range become larger, for 2001 to 2002 this year range, would this person be counted as a head count?
If the definition of HC is different between month and year, then we might need to add another if to deduct the leavers.
Using the
LV Headcounts = CALCULATE(COUNT('June - Data'[Start Date]),
FILTER('June - Data',
IF( LASTDATE('Date'[Date])-FIRSTDATE('Date'[Date])<=30,
IF(value([End date])<>0,[Start Date] <= LASTDATE('Date'[Date]) && [End Date]>=FIRSTDATE('Date'[Date]),[Start Date] <=LASTDATE('Date'[Date]) //for month
),
IF(value([End date])<>0,[Start Date] <= LASTDATE('Date'[Date]) && [End Date]>=LASTDATE('Date'[Date]), [Start Date] <=LASTDATE('Date'[Date]) //for year
)
)
)
)
This would decute those who leaves before the end of the year.
Regards
Microsoft EmployeeSimon,
Missing some counts should be related with the empty value of Enddate, change the formula with the one below:
Headcounts = CALCULATE(COUNTROWS('June - Data'),
FILTER('June - Data', IF(value([Enddate])<>0,[StartDate] <= LASTDATE('Date'[Date])
&& [EndDate]>= FIRSTDATE('Date'[Date]),[StartDate] <= LASTDATE('Date'[Date]))))
To remove the count for those people who leaves on the first day of a date filter context, remove the = under
[EndDate]>= FIRSTDATE('Date'[Date]).
See if it would work this time.
If any further help needed, please feel free to post back.
Regards
Hi Michael,
I feel like we are getting closer I've used the same formula but however the result now seems to be count a few hundrew higher now and we seems to have a bar for 2017 and 2018 too now?
The trend which is should start in 1993 with around 5 employee is at 280, and the current which should be around 1200 - 1300 is at 1900ish. I feel like we may still rolling the count for leavers after the month ends.
Thank you for your help so far really do appreciate it,
Cheers
Simon
Further to the above - when I drill down to each month there always seems to be a addition of "278" employee then the fact. However when drill back up to the full years number is larger then that.
I then realised that its because there are some null rows in my data exactly 278 of them, and because the formula COUNTROW will count these also, I've changed it to
LV Headcounts = CALCULATE(COUNT('June - Data'[Start Date]), FILTER('June - Data', IF(value([End date])<>0,[Start Date] <= LASTDATE('Date'[Date]) && [End Date]>=FIRSTDATE('Date'[Date]),[Start Date] <=LASTDATE('Date'[Date]))))
Which now gives me the right numbers by months but when I drill up, when viewed by years, the total seems to be a few hundred higher for the largers years. I'm think the leavers not being deducted for when its in years view?
CheersSimon
Please advise :)
CheersSimon
- v-micsh-msft
Simon,
Good Point about replacing the countrows function here.
For the year HC, I have some consideration here. Let's say one person who joins at 2001.1.1 and leaves at 2001.1.31, when under month range, we will count this one as a head count, right? But when the time range become larger, for 2001 to 2002 this year range, would this person be counted as a head count?
If the definition of HC is different between month and year, then we might need to add another if to deduct the leavers.
Using the
LV Headcounts = CALCULATE(COUNT('June - Data'[Start Date]),
FILTER('June - Data',
IF( LASTDATE('Date'[Date])-FIRSTDATE('Date'[Date])<=30,
IF(value([End date])<>0,[Start Date] <= LASTDATE('Date'[Date]) && [End Date]>=FIRSTDATE('Date'[Date]),[Start Date] <=LASTDATE('Date'[Date]) //for month
),
IF(value([End date])<>0,[Start Date] <= LASTDATE('Date'[Date]) && [End Date]>=LASTDATE('Date'[Date]), [Start Date] <=LASTDATE('Date'[Date]) //for year
)
)
)
)
This would decute those who leaves before the end of the year.
Regards
Thank you so much!
This is working perfectly - you are amazing at this :)I've learnt a lot from this.
CheersSimon