Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
NovWor
Frequent Visitor

Creating headcount growth adding each month hires to previous (line chart)

Hi, this should be fairly easy, but I am a beginner!

My data has hire month for employees,

  • I want to show a liner headcount of total employees each month
  • I need to start with a fixed number of head count then add
    For example before year 2022 we have 100 employees already, how can this be more user friendly to be changed by the user? 

 

Example

EmployeeHire dateOther filter
Worker 1    01/02/2022    Filled  
Worker 2   01/02/2022Accepted
Worker 302/11/2022Filled
Worker 403/22/2022Filled
Worker 503/25/2022Filled

 

What I need is to show is Total Head Count measure.. for Jan = 102, Feb = 103, Mar = 105 (so it adding number of hires from previous months)

I have a DateDim table... (if that's useful?)

Also, what if I want to calculate only the ones that are (Filled)?

 

Thank you in advance! 

2 REPLIES 2
NovWor
Frequent Visitor

@amitchandak thank you for your quick response.. 

I do not have termination date, and I tried your calculation like this:

 

HireTotals = CALCULATE(Count('Table'[Hire date]),filter(DimDate,DimDate[Date] <=maxx(DimDate,DimDate[Date]))) + [initial Emp]

 

And I got this (total by month) not the (total year to date) = adding totals per month

NovWor_0-1658867563824.png

 

I tried to use the quick measure function to measure YTD and it came up with this expression

Hire date YTD =
IF(
    ISFILTERED('Table'[Hire date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    TOTALYTD(
        COUNTA('Tabel'[Hire date]),
        'Table'[Hire date].[Date]
    ) + [ i added intial head count here]
)
It worked on it's own but it won't change based on other visuals and gives the error msg 
 
NovWor_1-1658868115088.png

 

 

Any thoughts?

Thank you!

amitchandak
Super User
Super User

@NovWor , if we have hire and termination date both then we use, log like the one here

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

else we use logic like

[Intial Emp] + CALCULATE(Count(Hire[EmpID]),filter(date,date[date] <=maxx(date,date[date]))) - CALCULATE(SUM(leaver[leaver]),filter(date,date[date] <=maxx(date,date[date])))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors