Comulative total of employee

[Anonymous]

Hello,

I have some strugle while calculating the comulative total active employee by year.

There are two tables:

• Employee (No, Data_Joined, Data_Left)
• Calendar (Date, Year)

Two realtionships:

• Active: Employee[Data_joined], Calendar[Date]
• Not active: Employee[Data_left], Calendar[Date]

I have created two measures:

Total emplyee = 
    CALCULATE(
        COUNTA(Employee[No_]);
        FILTER(ALL('Calendar');
       'Calendar'[Date]<=MAX('Calendar'[Date])))

Left Employee = 
    CALCULATE(
        COUNTA(Employee[No_]);
        FILTER(ALL('Calendar');
       'Calendar'[Date]<=MAX('Calendar'[Date]));USERELATIONSHIP(Employee[Data_Left];'Calendar'[Date]))

But something wrong with left employee calculations.

there is a sample pbix file: https://www.dropbox.com/s/kh7cj7cjbpy2oih/Employee%20count.pbix?dl=0

Could anyone help me with this issue?

[Gopal30]

I am not sure if you are looking for this

Left Employee =

CALCULATE(

COUNTA(Employee[No_]),

FILTER(ALL('Calendar'),

'Calendar'[Date]<=MAX('Calendar'[Date])),FILTER(Employee,Employee[Data_Left]))

[Anonymous]

Thanks, @Gopal30

But I need to calculate amount of employee by the date they left, not by the date that the fired empyeewas hired.

For example:

In 2011 year, there where 71 employees but non of them left the company, so total active employee number should be 71.

In 2012 there where 3 new employees and one left, so total comulative emplyees should be 74 of emplyes and one left. Toatal amount of active employees 71+3-1 =73  and so on.

[AlB · Accepted solution]

Hi @Anonymous 

Try this small change for your second measure:

Left Employee = 
    CALCULATE(
        COUNTA(Employee[Data_Left]);
        FILTER(ALL('Calendar');
       'Calendar'[Date]<=MAX('Calendar'[Date]));USERELATIONSHIP(Employee[Data_Left];'Calendar'[Date]))

[Anonymous]

Thanks, @AlB , it worked!