# Power BI Exchange

View Only

## Calculate Employee Count

• #### 1.  Calculate Employee Count

Posted 16 days ago
I have this data (attached) of employee joining and leaving years. Attached picture is what i want to create:

------------------------------
------------------------------

Attachment(s)

• #### 2.  RE: Calculate Employee Count

Bronze Contributor
Posted 14 days ago
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 ?

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
------------------------------

• #### 3.  RE: Calculate Employee Count

Posted 14 days ago

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

------------------------------
Senior Finance Analyst
------------------------------

Attachment(s)

EmployeeCount.pbix   58 KB 1 version

• #### 4.  RE: Calculate Employee Count

Posted 11 days ago
Hi Gulfam, If you encounter an error, you may use the revised file attached.

------------------------------
Senior Finance Analyst
------------------------------

Attachment(s)

EmployeeCount.pbix   58 KB 1 version

• #### 5.  RE: Calculate Employee Count

Posted 13 days ago
Veja se o anexo ajuda

------------------------------
Vilmar Santos
------------------------------

Attachment(s)