Home Forums Power Pivot Calculate headcount based start end date range

Tagged: 

This topic contains 13 replies, has 2 voices, and was last updated by  tomallan 8 months, 1 week ago.

Viewing 14 posts - 1 through 14 (of 14 total)
  • Author
    Posts
  • #8876

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    I have in a Excel file 2 tab with employee`s data:

    1. Current = Personal data ( enrollment, name, gender, DOF, age, ….)
    2. Start/End = Contract information (enrollment, Start date, End date, leaving reason….)

    In Power Pivot I created :

    – A data model with the 2 Tabs (current / StarEnd)
    – Calendar tab (from 01/01/2015 up to now)
    – Relationship between the 2 tab (current and StarEnd) using the Enrollment that are similar in both tab (Étiquettes de lignes).

    Now I`d like to calculate the headcounts (Nb of employees actives) splited by month (axis categories) from the 2015 up to now (axis Series) in a Pivot Graphic, which can be able to use some filters like Gender, Classification and etc…

    Graph_example_Id_like_to_build

    See the Excel file that I’m working on EXCEL FILE_POWER PIVOT

    #8877

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    Rich,

    It looks like the Start/End table contains the employment history for the individuals on Current. For example, for Bruno Bruno (87240216) on the Current table, there are five records in the Start/End table, which consist of the ranges (date format used below is mm/dd/yyyy):

    1/5/2004 to 4/30/2013,
    5/1/2013 to 3/31/2015,
    4/1/2015 to 9/30/2015
    10/1/2015 to 7/31/2017
    8/1/2017 to (blank, which I believe means the person is still part of the company)

    Are my assumptions correct so far?

    Tom

    #8878

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    Hi Tomallan,

    Thanks for your quickly reply.

    That’s right.

     

    #8879

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    OK. I have a solution, but there is something to talk about first.

    Before Jan 1, 2015 (the start of the Calendar table), there are 412 employees/contractors in the startend table that began their contract prior to the earliest calendar table date and either had a blank in the administrative end date (contract continues) or the contract ends sometime after Jan 1, 2015.

    You can use this measure to confirm:

    How Many Hired Before Calendar Start:=CALCULATE (
        DISTINCTCOUNT ( StartEnd[Étiquettes de lignes] ),
        FILTER (
            StartEnd,
            StartEnd[Administrative start date] < DATE ( 2015, 1, 1 )
                && (
                    ISBLANK ( StartEnd[Administrative End Date] )
                        || StartEnd[Administrative End Date] >= DATE ( 2015, 1, 1 )
                )
        )
    )

    The implication here is that if your business has a steady rise and fall in contracts started and contracts ended, you will not see an active head count of less than 400 in this dataset.

    In addition, if you decide to create relationship(s) between the startend table and the calendar table, it will be a good idea to either use inactive relationships between the calendar table and the startend table (or create a different calendar table to relate to startend), otherwise it will affect the results of the head count measure. If you choose the option with inactive relationships between the calendar table and the startend table, you can use the USERELATIONSHIP function within a CALCUALTE to activate a relationship on an as-needed basis.

    Also, I recommend creating a sort order column in the calendar table for your MMM-YYYY column, such as:

    Sort by Column for MMM-YYYY=FORMAT([Date],"YYYY-mm")

    Also, to preserve the consistency of the results, I do not use functions like TODAY() which will alter results if an old workbook is opened at a later date. Instead I use the concept of a “Data Date” (date the data was “fresh”), which in this workbook looks like this:

    Data Date:=LASTDATE ( ALL ( StartEnd[Administrative start date] ) )

    Finally, after testing, here is the measure that worked for me, after putting MMM-YYYY on pivot columns:

    Head Count by Calendar :=
    IF (
        [Data Date] >= MAX ( 'Calendar'[Date] ),
        CALCULATE (
            DISTINCTCOUNT ( 'Current'[Étiquettes de lignes] ),
            FILTER (
                'Current',
                COUNTROWS (
                    FILTER (
                        RELATEDTABLE ( StartEnd ),
                        StartEnd[Administrative Start Date] <= MAX ( Calendar[Date] )
                            && (
                                ISBLANK ( StartEnd[Administrative End Date] )
                                    || StartEnd[Administrative End Date] >= MIN ( Calendar[Date] )
                            )
                    )
                )
                    > 0
            )
        )
    )
    

    Here is the logic:

    The expression [Data Date] >= MAX ( ‘Calendar'[Date] ), is a way of including only completed months. If you want the latest (partial) month with any data, replace the MAX with a MIN.

    The DISTINCTCOUNT makes certain that within the results of the formula, we will not include more than 1 record per person.

    Finally, the filter expression tests each record in the Current if any of its related records overlaps with the min and max of filter context coming from the calendar table. Blank end dates are handled as not yet complete (still in effect). A head is counted if at least one of their contracts has a date within the current date filter context.

    The concept of overlap is expressed as:

    Given two date ranges (A and B)

    If the first date of range A occurs before the last date of range B, and the last date of range A occurs after the start of range B, the two ranges overlap.

    The formula will work well with slicers as long as the slicers filter the Current table.

    Tom

    #8888

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    Tomallan,

    Thanks a lot, it worked perfectly. I just have 2 questions to improve it:

    1. How can count and show the data for JAN-2018 , its just showing headcount from JAN 2015 up to DEC 2017.

     

    1. how can i set to not count the employees that left in the middle of month. Example: someone that left 10-12-2017, to not count in Dec headcount.
    #8889

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    For 1., just change

    [Data Date] >= MAX ( 'Calendar'[Date] ),

    to

    [Data Date] >= MIN ( 'Calendar'[Date] ),

    For 2., do you also want to exclude people that started somewhere in the middle of a period, or just exclude the people with contracts that ended in the middle of a period?

    Also, what version of Excel are you using (2010, 2013 or 2016)?

    #8894

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    Thanks a lot !

    Exclude just people that ended in the middle of the month, the starters should count.

    The concept of Headcount is people that are active or ended contract at last day of the month.

    I`m using Excel 2016.

    #8895

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    Tomallan,

    Just another question, how can create a measure to count the starts and the ended ?

    I try to use the same syntax as “How Many Hired Before Calendar ” making some modifications but it`s give me error.

    #8899

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468
    Head Count by Calendar (excluding contract end date before period end date):=IF (
        [Data Date] >= MIN ( 'Calendar'[Date] ),
        CALCULATE (
            DISTINCTCOUNT ( 'Current'[Étiquettes de lignes] ),
            FILTER (
                'Current',
                COUNTROWS (
                    FILTER (
                        RELATEDTABLE ( StartEnd ),
                        StartEnd[Administrative Start Date] <= MAX ( Calendar[Date] )
                            && (
                                ISBLANK ( StartEnd[Administrative End Date] ) 
                                    || StartEnd[Administrative End Date] >= MAX ( Calendar[Date] )
                            )
                    )
                )
                    > 0
            )
        )
    )
    Contracts Started:=VAR vPeopleWithContractsStarting =
        CALCULATETABLE (
            VALUES ( 'Current'[Étiquettes de lignes] ),
            FILTER (
                'Current',
                COUNTROWS (
                    FILTER (
                        RELATEDTABLE ( StartEnd ),
                        StartEnd[Administrative start date] >= MIN ( Calendar[Date] )
                            && ( StartEnd[Administrative start date] <= MAX ( Calendar[Date] ) )
                    )
                )
                    > 0
            )
        )
    RETURN
      IF ( [Data Date] >= MIN ( 'Calendar'[Date] ) && NOT ( ISBLANK ( MIN ( 'Calendar'[Date] ) ) ) , COUNTROWS ( vPeopleWithContractsStarting ), BLANK() )
    Contracts Ended:=VAR vPeopleWithContractsEnding =
        CALCULATETABLE (
            VALUES ( 'Current'[Étiquettes de lignes] ),
            FILTER (
                'Current',
                COUNTROWS (
                    FILTER (
                        RELATEDTABLE ( StartEnd ),
                        StartEnd[Administrative End Date] >= MIN ( Calendar[Date] )
                            && ( StartEnd[Administrative End Date] <= MAX ( Calendar[Date] ) )
                    )
                )
                    > 0
            )
        )
    RETURN
      IF ( [Data Date] >= MIN ( 'Calendar'[Date] ) && NOT ( ISBLANK ( MIN ( 'Calendar'[Date] ) ) ), COUNTROWS ( vPeopleWithContractsEnding ) )
    #8900

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    Tomallan,

    thanks so much !

    It works perfectly…. now I started to understand the logical about the Power Pivot.

    It’s a bit different from the Excel way of calculate.

     

    thanks !

    #8903

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    Tomallan,

    Once again thanks a lot for your help, and sorry for bother you another time.

    I add a new table in the source data called COSTCENTRE, in this table has splited the cost centre strucutre (operational code, activity and etc…), I’d like also use theses content as filter for the graph.

    I create a relation between ‘Current'[Cost Centre] & COSTCENTRE’ [CC_code] and put in the graph the ‘COSTCENTRE’ [Activity] as Filter but when I click on the filter (slicer) it’s return blank ….

    • COSTCENTRE’ [CC_code] is written as the ‘Current'[Cost Centre]
    • COSTCENTRE’ [CC_code] is unique, so it’s not repeat

     

    #8913

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    When an empty result set is an error, it usually means there is not an exact match between the ID column in the parent table and the linked column in the child table.

    I worked with such a case last week, when the code in the parent table looked like this: 00-00.00-00, but in the child table the linked column values looked like this: 00-00-00-00 (this delimiter used in the middle character was different).

    One way you can test for this case, is if you make a calculated column in the Current table with this formula:

    = RELATED ( ‘COSTCENTRE'[CC_code] )

    Wherever you see a blank in that calculated column, there is no matching record in the parent table.

    You could also make a similar type of calculated column in the CostCentre table (uses RELATEDTABLE instead of RELATED):

    = COUNTROWS ( RELATEDTABLE ( ‘Current’ ) )

    and wherever that calculated column in the CostCentre table was either 0 or blank, there are no matching records in the Current table.

    Give the above a try and let me know the results.

    Tom

    #8919

    Rich_Coutinho
    Participant
    • Started: 9
    • Replies: 16
    • Total: 25

    Hi Tom,

    Thanks, it’s working now, the problem was as you said:  parent table looked like this: 00-00.00-00, but in the child table the linked column values looked like this: 00-00-00-00.

    I corrected the child table, and now it’s working.

     

    You helped me a lot, thanks so much.

    #8920

    tomallan
    Keymaster
    • Started: 7
    • Replies: 2461
    • Total: 2468

    Rich,

    Thanks again for the kind words!

    Please let your Power Pivot and Power BI friends know where they can also find help with their DAX and data models.

    Looking forward to working with you again in the PowerPivotPro forums,

    Tom

Viewing 14 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic.