Good Day All,
I am struggling with adapting the Current Staff calculation I picked up from this YouTube video by Sam
I am working from excel files held in Sharepoint online. I have uploaded a “demo” model with the tables and relationships relevant to the calculation I am struggling with. SampleHR.pbix (141.3 KB)
I am provided with an employee census files that contains these relevant fields, among several others. One row for each employee:
I am also provided a terminations report on a monthly basis which I also pull from a SharePoint folder that merges each individual month of data into the terminations table within PowerBI. This table is linked to the Census table using a 1:1 bi-directional relationship. From this table I add a custom column to the Census table, [EndDate], using:
EndDate = Related(Terminations[TerminationDate])
I created a date table using CalendarAuto and added Month / MonthNo / Year columns.
All three dates in the Census table and the TerminationDate from the Terminations table are all connected with inactive links to the date table.
At initial trial, I had the need to modify the base formula from the YouTube video to function when an employee is hired and then terminates within the same month. The modification I made works in these cases.
My problem is that any re-hire is not counted, and I have not yet been able to figure out how to add this into the formula. The base formula is below, and my modification is in bold. A screen capture of my model is also below.
FILTER(VALUES(Census[HIRE DATE]) , Census[HIRE DATE] <= MAX(Dates[Date]) ),
Filter(VALUES(Census[EndDate]),OR**(AND(Census[EndDate] >= MIN(Dates[Date]), Census[EndDate]> MAX(Dates[Date]))**,ISBLANK(Census[EndDate]) ))
Any assistance in conquering this obstacle will be much appreciated; I have been struggling for many hours and just cannot seem to get there. The eventual goal for this report is to compare current staff to headcount needs and produce turnover ratio’s across time.