- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
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.
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi @Mateja
would you please provide some sampke dat snd advise the shape of the report you would like to produce.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 |
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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. 🙂
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Mahes0016
Please read my response to tamerj1.
Thank you for the help.

Helpful resources
Subject | Author | Posted | |
---|---|---|---|
12-13-2024 07:00 AM | |||
08-26-2024 12:58 AM | |||
01-08-2025 05:55 AM | |||
Anonymous
| 11-19-2021 03:42 AM | ||
04-24-2024 04:45 AM |
User | Count |
---|---|
19 | |
17 | |
14 | |
13 | |
11 |