Forum Discussion

Chippy's avatar
Chippy
Frequent Visitor
2 years ago
Solved

Monthly Headcount Chart in Power BI

Assistance required where I would like to display on a line chart in PowerBI the total company headcount, with one line displaying this year and another last year. as per below; the data I have is Ea...
Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    2 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

     

     

     

     

     

    last year count: = 
VAR _year =
    MAX ( 'Calendar'[Year] )
VAR _t =
    FILTER (
        Data,
        Data[Joined Date]
            <= MAXX ( FILTER ( 'Calendar', 'Calendar'[Year] = _year ), 'Calendar'[Date] )
            && OR (
                Data[Termination Date]
                    >= MINX ( FILTER ( 'Calendar', 'Calendar'[Year] = _year ), 'Calendar'[Date] ),
                ISBLANK ( Data[Termination Date] )
            )
    )
RETURN
    COUNTROWS ( _t )

     

    previous year count: = 
VAR _year =
    MAX ( 'Calendar'[Year] ) - 1
VAR _t =
    FILTER (
        Data,
        Data[Joined Date]
            <= MAXX ( FILTER ( 'Calendar', 'Calendar'[Year] = _year ), 'Calendar'[Date] )
            && OR (
                Data[Termination Date]
                    >= MINX ( FILTER ( 'Calendar', 'Calendar'[Year] = _year ), 'Calendar'[Date] ),
                ISBLANK ( Data[Termination Date] )
            )
    )
RETURN
    COUNTROWS ( _t )

     

  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi, Chippy 

    Thanks for Jihwan_Kim's reply. In my personal opinion the response he gave was excellent, what questions do you have about the results he gave or the results that didn't meet your expectations, you can elaborate on your needs. Feel free to help you.

    Best Regards,
    Yang
    Community Support Team

Chippy's avatar
Chippy
Frequent Visitor
2 years ago

Thanks for your solution; How can I tweek this a bit as I just need to edit this slightly. For instance for the month of April 24, the active headcount would be:

 

April 24 = Total User Count - (Count of all New Starters > April 24) - (Count of Terminations <= April 24), so that is:

 

161-27-38 =96

 

So I count anyone who has started in that month, but I dont count somebody if they have been terminated that month. 

 

Thanks for your patience, I am still fairly new to PowerBI!

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Chippy 

    Thanks for Jihwan_Kim's reply. In my personal opinion the response he gave was excellent, what questions do you have about the results he gave or the results that didn't meet your expectations, you can elaborate on your needs. Feel free to help you.

    Best Regards,
    Yang
    Community Support Team