Calculate Employee Count

Calculate Employee CountFollow
Gulfam Ahmad
Gulfam AhmadNov 16, 2022 01:15 AM
I have this data (attached) of employee joining and leaving years. Attached picture is what i want to …
1. Calculate Employee Count

Gulfam Ahmad
Posted Nov 16, 2022 01:15 AM | view attached
I have this data (attached) of employee joining and leaving years. Attached picture is what i want to create:

Can you please help me calculate the total employees (cumulative) in each year:
Employee Count

——————————
Gulfam Ahmad
Head of Analytics
——————————
Attachment(s)

xlsxEmployee Count for Each Year.xlsx

2. RE: Calculate Employee Count

Bronze Contributor
James Watts
Posted Nov 18, 2022 04:12 AM
First create a list of the years from start to end:

Years = GENERATESERIES(MIN(‘SourceData'[Joining_Year]),Max(‘SourceData'[Leaving_year]))
Then add a measure to this to add up all the starters before or equal to this year, and all the leavers before or after this year

(or leavers before this year ?

HeadCount =
VAR CurrentYear = SELECTEDVALUE(‘Years'[Value])
RETURN
CALCULATE(COUNT(SourceData[Joining_Year]),ALL(SourceData),’SourceData'[Joining_Year]<= CurrentYear) – CALCULATE(COUNT(SourceData[Leaving_Year]),ALL(SourceData),’SourceData'[Leaving_Year]<= CurrentYear)

——————————
James Watts
Business Intelligence Analyst
——————————

3. RE: Calculate Employee Count

Rd 3nidad
Posted Nov 18, 2022 04:52 AM | view attached
Hi Gulfam,

You can create a year table and connect or link your joining year as active link and leaving year as inactive to the year table, then create a formula:

CumCount =

var J = CALCULATE(COUNT(Emp[Unique_Key]),’Year'[Value]<=max(‘Year'[Value]))
var YV = VALUES(‘Year'[Value])
var L =
CALCULATE(COUNT(Emp[Leaving_year]),
USERELATIONSHIP(Emp[Leaving_year],’Year'[Value])
,’Year'[Value]<=yv
)

Var R = j-l

Return

R

Hope this help.

Regards,
Ardie

——————————
Rd 3nidad
Senior Finance Analyst
——————————

Attachment(s)

pbixEmployeeCount.pbix

4. RE: Calculate Employee Count

Rd 3nidad
Posted Nov 21, 2022 02:02 AM | view attached
Hi Gulfam, If you encounter an error, you may use the revised file attached.

——————————
Rd 3nidad
Senior Finance Analyst
——————————

Attachment(s)

pbixEmployeeCount.pbix

5. RE: Calculate Employee Count

Vilmar Santos
Posted Nov 18, 2022 07:53 PM | view attached
Veja se o anexo ajuda

——————————
Vilmar Santos
——————————

Attachment(s)

pbixEmployee Count for Each Year.pbix