Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mateja
Helper II
Helper II

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

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!
1 ACCEPTED SOLUTION

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

1.png2.png

View solution in original post

6 REPLIES 6
tamerj1
Super User
Super User

Hi @Mateja 

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

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! 

 

DateEmp NoHeadcountSeparationsLast Day In Month
2018-03-01120.170FALSE
2018-03-011210TRUE
2018-04-01120.661FALSE
2018-04-011210TRUE
2018-05-011210FALSE
2018-05-011210TRUE
2018-06-011210FALSE
2018-06-011210TRUE
2018-07-011210FALSE
2018-07-011210TRUE
2018-08-011210FALSE
2018-08-011210TRUE
2018-09-01120.141FALSE
2019-03-01120.130FALSE
2019-03-011210TRUE
2019-04-011210FALSE
2019-04-011210TRUE
2019-05-011210FALSE
2019-05-011210TRUE
2019-06-011210FALSE
2019-06-011210TRUE
2019-07-011210FALSE
2019-07-011210TRUE
2019-08-011210FALSE
2019-08-011210TRUE
2019-09-011210FALSE
2019-09-011210TRUE
2019-10-011210FALSE
2019-10-011210TRUE
2019-11-011210FALSE
2019-11-011210TRUE
2019-12-011210FALSE
2019-12-011210TRUE
2020-01-011210FALSE
2020-01-011210TRUE
2020-02-011210FALSE
2020-02-011210TRUE
2020-03-011210FALSE
2020-03-011210TRUE
2020-04-011210FALSE
2020-04-011210TRUE
2020-05-011210FALSE
2020-05-011210TRUE
2020-06-011210FALSE
2020-06-011210TRUE
2020-07-01120.61FALSE
2021-09-01120.10FALSE
2021-09-011210TRUE
2021-10-011210FALSE
2021-10-011210TRUE
2021-11-011210FALSE
2021-11-011210TRUE
2021-12-011210FALSE
2021-12-011210TRUE
2022-01-011210FALSE
2022-01-011210TRUE
2022-02-01120.371FALSE
2021-07-01180.770FALSE
2021-07-011810TRUE
2021-08-011810FALSE
2021-08-011810TRUE
2021-09-011810FALSE
2021-09-011810TRUE
2021-10-011810FALSE
2021-10-011810TRUE
2021-11-011810FALSE
2021-11-011810TRUE
2021-12-011810FALSE
2021-12-011810TRUE
2022-01-011810FALSE
2022-01-011810TRUE
2022-02-011810FALSE
2022-02-011810TRUE
2022-03-011810FALSE
2022-03-011810TRUE
2022-04-011810FALSE
2022-04-011810TRUE
2022-05-011810FALSE
2022-05-011810TRUE
2022-06-011810FALSE
2022-06-011810TRUE
2022-07-011810FALSE
2022-07-011810TRUE
2022-08-01180.071FALSE
2022-11-01180.240FALSE
2022-11-011810TRUE
2022-12-011810FALSE
2022-12-011810TRUE
2023-01-01180.570FALSE

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

1.png2.png

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

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

Hi Mahes0016 

 

Please read my response to tamerj1.

Thank you for the help. 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors