Counting each working period of employees when they have few separations from the company

[Mateja]

Hi everyone

I need to make a calculated column for months employed based on a number of rows an employee has before his separation. 

The calculation needs to restart every time he comes back to the company.

CALCULATE(DistinctCOUNT(F_Employment[Date]), F_Employment[Headcount] > 0 && F_Employment[Separations] = 0)

I need to use distinct count for month column because there are mostly two rows per employee; one row is for the last day in the month value  and the other one is for the aggregated values of the months.
In the measure above are the basic conditions I need to apply but I do not know how to formulate a measure so it restarts everytime the separation is 1.

I I also have a date filter on my main page meaning users can switch between different months on the Dasboard.

I really appreciate your help!

[tamerj1]

Hi @Mateja 

would you please provide some sampke dat snd advise the shape of the report you would like to produce. 

[Mateja]

Hi tamerj1 and Mahesh0016

I am attaching some sample data with relevant columns.

AsI wrote, I want to count the number of months employee was in company before leaving (Separation = 1).

Due to using data in the graph I want to make a calculated column because I am interested in those who left the company in under 12 months. meaning the number of monrhs in the calculated column per row needs to increase with every month employee spends in company.

I want the calculation to restart  everytime the employee comes back to the company because we have people coming back so I do not want their previous period calculated in the period after.

In the table you can see every employee has two lines, one for avg month and one only for the last day in month. If the Headcount value is bigger than 0 it means the employee is in the company. When the Headcount is 0 and Separation is 1 the employee left the company.

My table is connected to the Date table.

I relly appreciate your help! 

Date	Emp No	Headcount	Separations	Last Day In Month
2018-03-01	12	0.17	0	FALSE
2018-03-01	12	1	0	TRUE
2018-04-01	12	0.66	1	FALSE
2018-04-01	12	1	0	TRUE
2018-05-01	12	1	0	FALSE
2018-05-01	12	1	0	TRUE
2018-06-01	12	1	0	FALSE
2018-06-01	12	1	0	TRUE
2018-07-01	12	1	0	FALSE
2018-07-01	12	1	0	TRUE
2018-08-01	12	1	0	FALSE
2018-08-01	12	1	0	TRUE
2018-09-01	12	0.14	1	FALSE
2019-03-01	12	0.13	0	FALSE
2019-03-01	12	1	0	TRUE
2019-04-01	12	1	0	FALSE
2019-04-01	12	1	0	TRUE
2019-05-01	12	1	0	FALSE
2019-05-01	12	1	0	TRUE
2019-06-01	12	1	0	FALSE
2019-06-01	12	1	0	TRUE
2019-07-01	12	1	0	FALSE
2019-07-01	12	1	0	TRUE
2019-08-01	12	1	0	FALSE
2019-08-01	12	1	0	TRUE
2019-09-01	12	1	0	FALSE
2019-09-01	12	1	0	TRUE
2019-10-01	12	1	0	FALSE
2019-10-01	12	1	0	TRUE
2019-11-01	12	1	0	FALSE
2019-11-01	12	1	0	TRUE
2019-12-01	12	1	0	FALSE
2019-12-01	12	1	0	TRUE
2020-01-01	12	1	0	FALSE
2020-01-01	12	1	0	TRUE
2020-02-01	12	1	0	FALSE
2020-02-01	12	1	0	TRUE
2020-03-01	12	1	0	FALSE
2020-03-01	12	1	0	TRUE
2020-04-01	12	1	0	FALSE
2020-04-01	12	1	0	TRUE
2020-05-01	12	1	0	FALSE
2020-05-01	12	1	0	TRUE
2020-06-01	12	1	0	FALSE
2020-06-01	12	1	0	TRUE
2020-07-01	12	0.6	1	FALSE
2021-09-01	12	0.1	0	FALSE
2021-09-01	12	1	0	TRUE
2021-10-01	12	1	0	FALSE
2021-10-01	12	1	0	TRUE
2021-11-01	12	1	0	FALSE
2021-11-01	12	1	0	TRUE
2021-12-01	12	1	0	FALSE
2021-12-01	12	1	0	TRUE
2022-01-01	12	1	0	FALSE
2022-01-01	12	1	0	TRUE
2022-02-01	12	0.37	1	FALSE
2021-07-01	18	0.77	0	FALSE
2021-07-01	18	1	0	TRUE
2021-08-01	18	1	0	FALSE
2021-08-01	18	1	0	TRUE
2021-09-01	18	1	0	FALSE
2021-09-01	18	1	0	TRUE
2021-10-01	18	1	0	FALSE
2021-10-01	18	1	0	TRUE
2021-11-01	18	1	0	FALSE
2021-11-01	18	1	0	TRUE
2021-12-01	18	1	0	FALSE
2021-12-01	18	1	0	TRUE
2022-01-01	18	1	0	FALSE
2022-01-01	18	1	0	TRUE
2022-02-01	18	1	0	FALSE
2022-02-01	18	1	0	TRUE
2022-03-01	18	1	0	FALSE
2022-03-01	18	1	0	TRUE
2022-04-01	18	1	0	FALSE
2022-04-01	18	1	0	TRUE
2022-05-01	18	1	0	FALSE
2022-05-01	18	1	0	TRUE
2022-06-01	18	1	0	FALSE
2022-06-01	18	1	0	TRUE
2022-07-01	18	1	0	FALSE
2022-07-01	18	1	0	TRUE
2022-08-01	18	0.07	1	FALSE
2022-11-01	18	0.24	0	FALSE
2022-11-01	18	1	0	TRUE
2022-12-01	18	1	0	FALSE
2022-12-01	18	1	0	TRUE
2023-01-01	18	0.57	0	FALSE

[tamerj1 · Accepted solution]

@Mateja 
Something like this? (please refer to attached sample file)

[Mateja]

I can not thank you enough! This is exactly what I have been looking for. I am still in the process of learning so I really appreciate the time you used to help me.

I hope it is ok, I will tag you on another DAX post that I am currently trying to solve. 🙂

[Mahesh0016]

@Mateja please try below furmula

CALCULATE(DistinctCOUNT(F_Employment[Date]), F_Employment[Headcount] > 0 && F_Employment[Separations] < 1)

@Mateja if this post not helps so please share sample data and sample output in table format.

[Mateja]

Hi Mahes0016 

Please read my response to tamerj1.

Thank you for the help.