Forum Discussion

bricorb's avatar
bricorb
Regular Visitor
4 years ago
Solved

Current and Future Headcount

I am very new to PowerBi and data analytics.  I am trying to create a report that tracks actual headcount and future projected headcount.  The projected headcount (hiring plan) is based on a simple table of "discipline" and "estimated hires"

 

I also have a table of current and past employees with Hire Date, Graduation Date, and Termination date, among many other details.

 

I have a report that is that show headcount vs department by month/year.

 

What I am missing is the future projected headcount.  Ideally I would like to add it to my existing report as a new line, Starting with the next month, but it could be a new report as well for just projected headcount.  I have other future projections will need to be added to that report as well, but they should be relatively the similar measures as future new hires.  These other measures would be for projected terminations (Termination rate per discipline) and estimated graduations (Estimated Discipline Duration).

 

TIA for any help on how create a measure for future headcount based on hiring plan per discipline.

 

This is my measure calcualtion for current headcount:

Headcount = 
VAR _Headcount = CALCULATE(
    COUNTX(
        FILTER(
            Associates,
            Associates[Start Date]<=MAX('Calendar'[Date]) &&
            ((ISBLANK(Associates[Term/Transfer Date]) &&
            ISBLANK(Associates[Promotion Date])) ||
            (Associates[Term/Transfer Date] > MAX('Calendar'[Date]) || Associates[Promotion Date]>MAX('Calendar'[Date])))
        )
        ,Associates[Full Name]
        )
)
RETURN
IF(ISBLANK(_Headcount),0,_Headcount)



Here is my current report and data model:

1 Reply