Number of Employee - Start of the month

[corange]

Hi POWER BI Community, 

I need your help with a formula. I would like to know the number of employee I have got at the start of any given month. This is the formula I am currently using: 

SubcontractorMonthStart =

VAR MaxDate = CALCULATE(MIN('Calendar'[Date]),ALLEXCEPT('Calendar','Calendar'[Month]))//STARTOFMONTH(Calendar'[Date])

VAR EMPCOUNT =

CALCULATE(COUNTROWS(CALCULATETABLE(Drivers, Drivers[StartDate] <= MaxDate, ALL ('Calendar'[Date]))),

(ISBLANK(Drivers[FinishDate])|| Drivers[FinishDate] > MaxDate))

RETURN IF(ISBLANK(EMPCOUNT), 0, EMPCOUNT)

The thing is that formula will also took into consideration if an employee is hired on the first day of the month. In the example below (highlighted), you can see that by substracting leavers and adding new starters from the previous month, you don't get the right number for August and this is due to a new recruit on 01/09. It is almost like I need to adapt the formula to give me the count of employee on the last day of the previous month? 

Thanks for your help. 

[V-pazhen-msft]

@corange 

Can you provide a sample pbix, the SubcontractorMonthStart is not clear without a sample to test. 

Regards
Paul

[corange]

@V-pazhen-msft & @amitchandak

Hi,

Just following up on my post. Any feedbaack on how I could do it based on the sample file?

Thank you.

[corange]

Hi @V-pazhen-msft  & @amitchandak 

My apologies but I was on annual leave. Please find my sample here: https://1drv.ms/u/s!AiS7XVNuQsBCdEmmPcC7MgNIRqQ 

in the sample, you will see that the first row matches, (303 + 6 -16 =293). However, when we go to Sep 2019-2020 it doesnt (293-13+10 = 296. This is because we have that one employee (ID446)  that started on the 1st of September. I am more looking into having the count of employee for the very last day of the month rather than the start. 

Please let me know if you need more clarifications to help me out. 

Thanks. 

[amitchandak]

@corange , Your formula is not clear, but you can try firstnonblankvalue , that will take the first date, the second argument is your formula, which you can change

calculate( firstnonblankvalue('Calendar'[Date], countrows(Drivers)) ,ALLEXCEPT('Calendar','Calendar'[Month]))