Forum Discussion

simony's avatar
simony
Frequent Visitor
10 years ago
Solved

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

Cheers
Simon


Need Help
  • v-micsh-msft's avatar
    v-micsh-msft
    10 years ago

    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

  • v-micsh-msft's avatar
    v-micsh-msft
    Microsoft Employee
    10 years ago

    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

    Reports:

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

    Regards 

    • simony's avatar
      simony
      Frequent Visitor
      10 years ago

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

      Cheers

      Simon

      • v-micsh-msft's avatar
        v-micsh-msft
        Microsoft Employee
        10 years ago

        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