Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Head Counts by Month Troubles

    Posted Apr 07, 2020 06:12 AM
    I am trying to chart out our total employee head counts by month over time. I have to put several conditions in the filter to calculate it correctly based on our data. The conditions are as follows:
    1) The termination date field is blank, the rehire date field is blank and the hire date is <= that month.
    2) The termination date field is blank, The rehire date field is not blank and the rehire date field is <= that month
    3) The rehire date field is blank and (the hire date field is <= to that month and the termination date field is >= that month
    4) The rehire date field is not blank and (the rehire date field <= that month and the termination date field >= that month
    5) The rehire date field is not blank and the rehire date field is > the termination date field

    I created a custom column in the query editor for the Month and Year to use as the Axis in the visual.

    I have written the following expression to calculate this but it is not returning accurate data. Any ideas on what I am doing wrong?

    Employees by Month = CALCULATE(COUNTA(PRM_MASTER__EMPLOYEE[Employee]),
    FILTER(PRM_MASTER__EMPLOYEE,
    (
    (PRM_MASTER__EMPLOYEE[Rehire_Date] = BLANK() && PRM_MASTER__EMPLOYEE[Termination_Date] = BLANK() && PRM_MASTER__EMPLOYEE[Hire_Date <= MAX(PRM_MASTER__EMPLOYEE[MonthYear]))
    ||
    (PRM_MASTER__EMPLOYEE[Rehire_Date] <> BLANK() && PRM_MASTER__EMPLOYEE[Termination_Date] = BLANK() && PRM_MASTER__EMPLOYEE[Rehire_Date <= MAX(PRM_MASTER__EMPLOYEE[MonthYear]))
    ||
    (PRM_MASTER__EMPLOYEE[Rehire_Date] = BLANK() && (PRM_MASTER__EMPLOYEE[Hire_Date] <= MAX(PRM_MASTER__EMPLOYEE[MonthYear]) && PRM_MASTER__EMPLOYEE[Termination_Date] >= MAX(PRM_MASTER__EMPLOYEE[MonthYear])))
    ||
    (PRM_MASTER__EMPLOYEE[Rehire_Date] <> BLANK() && (PRM_MASTER__EMPLOYEE[Rehire_Date] <= MAX(PRM_MASTER__EMPLOYEE[MonthYear]) && PRM_MASTER__EMPLOYEE[Termination_Date] >= MAX(PRM_MASTER__EMPLOYEE[MonthYear])))
    ||
    (PRM_MASTER__EMPLOYEE[Rehire_Date] <> BLANK() && PRM_MASTER__EMPLOYEE[Rehire_Date] > PRM_MASTER__EMPLOYEE[Termination_Date])
    &&
    PRM_MASTER__EMPLOYEE[DBID] = "CompanyName"
    )

    ------------------------------
    Steve West
    Business Analyst
    Houston TX
    7134891437
    ------------------------------


  • 2.  RE: Head Counts by Month Troubles

    Gold Contributor
    Posted Apr 08, 2020 06:46 AM
    HI @Steve West,

    I think it could help if you could provide a sample data file to play with while trying to help you with the appropriate solution.
    You mention your DAX formula is not returning accurate data => What is it returning? And what would you be expecting? Some screenshot could help.

    Regards​

    ------------------------------
    David Gerard
    Business Intelligence Consultant
    Épinal
    ------------------------------



  • 3.  RE: Head Counts by Month Troubles

    Bronze Contributor
    Posted Apr 08, 2020 11:56 AM

    I had a similar scenario, here is the measure I created that works for me. I do have a calendar table, but it's not linked to the HRTable.  It looks like this:

    Calendar =
    ADDCOLUMNS(
    CALENDAR ( DATE (YEAR(MIN(HRTable[HireDate])), 1, 1 ), DATE ( YEAR(NOW()), 12, 31 ) ),
    "Date As Integer", FORMAT ( [Date], "MMDDYYYY" )
    )

    Head Count =

    CALCULATE (
    COUNTROWS ( HRTable ),
    FILTER (HRTable,
    (
    HRTable[HireDate] < MAX ( 'Calendar'[Date] )
    && HRTable[TermDate] >= MAX ( 'Calendar'[Date] )
    )
    || (
    HRTable[HireDate] < MAX ( 'Calendar'[Date] )
    && ISBLANK ( HRTable[TermDate] )
    )
    )
    )

    Hope this helps.

    ------------------------------
    Cheers,
    Mike
    ------------------------------



  • 4.  RE: Head Counts by Month Troubles

    Silver Contributor
    Posted Apr 09, 2020 09:55 AM
    Hi Steve,

    I agree with David.  It would help to see some examples, but I have a couple suggestions in the mean time.

    1. Your column PRM_MASTER_EMPLOYEE[MonthYear] - is this a full date field (mm/dd/yyyy) or just month and year (mm/yy) ?  You are using this in your condition statements, so I was wondering if the data does not match, so you would not get true results.

    2. To troubleshoot this, I would break it down into pieces, so you can test each filter you are using.  This should help you narrow down where your are having problems.


    ------------------------------
    Dan Edwards
    Senior Manager
    Crowe LLP
    Huntington Beach CA
    714-231-2202
    ------------------------------



  • 5.  RE: Head Counts by Month Troubles
    Best Answer

    Silver Contributor
    Posted Apr 10, 2020 12:41 PM
    Hi Steve. Here's a solution using a separate [Dates] table, 3 inactive relationships, and 4 measures. Hope it helps. Greg

    ------------------------------
    Greg Philps
    Power BI Consultant
    Ottawa ON Canada
    ------------------------------

    Attachment(s)

    xlsx
    Sample Employees.xlsx   9 KB 1 version


  • 6.  RE: Head Counts by Month Troubles

    Posted 22 days ago

    Hi Greg,

    how do i get Opening headcount for Month and closing headcount of month by Join date and exit date

    i have created calendar auto for dates creating a relation between join date and exit date.

    It will be helpful if i get opening fiscal year headcount and closing year headcount as well where fiscal year starts from April and ends in March.

    Have created a dax as below currently but the count seems to be wrong

    OpenHC = CALCULATE(
        COUNTX(FILTER('Master-Attrition','Master-Attrition'[Date Of Joining]<MIN('Calendar'[Date]) &&
        ( ISBLANK('Master-Attrition'[Date Of Exit])
        || 'Master-Attrition'[Date Of Exit]>=MIN('Calendar'[Date]))),
        ('Master-Attrition'[Employee Id])),CROSSFILTER('Master-Attrition'[Date Of Joining],'Calendar'[Date],None))
    Close HC = CALCULATE(
        COUNTX(FILTER('Master-Attrition','Master-Attrition'[Date Of Joining]<=MAX('Calendar'[Date]) &&
        (ISBLANK('Master-Attrition'[Date Of Exit])||
        'Master-Attrition'[Date Of Exit]>MAX('Calendar'[Date]))),
        ('Master-Attrition'[Employee Id])),CROSSFILTER('Master-Attrition'[Date Of Joining],'Calendar'[Date],None))



    ------------------------------
    Srinidhi MP
    560040
    ------------------------------