cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
simony Frequent Visitor
Frequent Visitor

Calculate HR trend graph

Hi All,

I'm having a bit of an issue working out the measure. 

So all I am trying to do is put together a trend graph for how many employees we have at each year, month & Quarter range (depend on selection but have the ability to do so), but keeping the ability to cut by slicers for info such as Departments etc. 

I have the following info in column: 

Start Date, 
End Date, 
Current or Historic 
Department

Been struggling on this for ages, would really appreciate any guidance, I've seen some simliar threads but couldn't quite apply on to my situation. 

Any help will be great Smiley Happy 

Cheers
Simon


1 ACCEPTED SOLUTION

Accepted Solutions
v-micsh-msft New Contributor
New Contributor

Re: 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

8 REPLIES 8
v-micsh-msft New Contributor
New Contributor

Re: Calculate HR trend graph

Hi simon,

Would the Drill through Report  with Date Hierarchy be helpful here?

Using Drill through report, it will allow us to see data within Year, Quarter or month range. For more information regarding Drill through report, please check:

Drill down in a visualization in Power BI

Regarding the headcount for a special date range, that depends on how you define if a user should be counted as Current when deal with EndDate/StartDate. If one should be counted in the month he join or leave, then we could write the measure using the following way:

1. First create a date table using the Startdate and enddate with the following formula:

Date = CALENDAR(MIN(Sheet1[Startdate]), MAX(Sheet1[Enddate]))

2. Add the Year and month column under Date table, each with the formula below:

    Month = MONTH('Date'[Date])

     Year = YEAR('Date'[Date])

3. After that, create a Measure under Usertable, with the formula below: 

Headcounts = CALCULATE(

                              COUNTROWS(Usertable),

                              FILTER(Usertable,

                                        [StartDate] <= LASTDATE('Date'[Date])&& [EndDate]>= FIRSTDATE('Date'[Date])

                                         )

                                )

Then after data modeling, under Report View, choose line and clustered column Chart, in the Fields on the left, select the date from date table and the measure Headcounts, then click on the write part, choose Slicer and select the department, see the result:

Data Model

1.PNG

Reports:

2.PNG

If any further assistance needed, please feel free to post back.

Regards 

simony Frequent Visitor
Frequent Visitor

Re: Calculate HR trend graph

Hi Michael, 

Firstly thank you so much for advice so far, definitely much further then how far I've got on this. 

But for some reason when I do that I am not getting the current number of employees, i'm missing a good few hours per year. 

I'm think this formula may not account for anyone that does not have a End date yet? or Maybe I've just explained this incorrectly (Sorry if I have). 

Current Employee = at any month he is still with us then he needs to be counted, for example if a person started with us on the 1st of January 2016 but have an end date on the 1st of June 2016, then in my trend graph he needs to be counted in Jan, Feb, March, April and May, but no longer counted in June. Note some people will not have a end date as they are not planning to leave so will always be counted. 

Hope i've explained it better or please do correct me if I've just completely wrong here. 

The was my version that was missing a few hundred people (haven't quite figured out why yet:

Headcountss = CALCULATE( COUNTROWS('June - Data'), FILTER('June - Data', [Start Date] <= LASTDATE('Date'[Date])&& [End Date]>= FIRSTDATE('Date'[Date]) ) )

Any advise will be much appreciated Smiley Happy 

Cheers

Simon

v-micsh-msft New Contributor
New Contributor

Re: Calculate HR trend graph

Simon,

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

 

simony Frequent Visitor
Frequent Visitor

Re: Calculate HR trend graph

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

simony Frequent Visitor
Frequent Visitor

Re: Calculate HR trend graph

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? 

Cheers

Simon

Please advise Smiley Happy 

Cheers

Simon

v-micsh-msft New Contributor
New Contributor

Re: 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

simony Frequent Visitor
Frequent Visitor

Re: Calculate HR trend graph

Thank you so much! 

This is working perfectly - you are amazing at this Smiley Happy 

 

I've learnt a lot from this. 

Cheers

Simon

v-micsh-msft New Contributor
New Contributor

Re: Calculate HR trend graph

You're welcome.Smiley Very Happy

If any further questions when using Power BI, please feel free to post.

We will always be happy to help.

Regards