Recently while working on a consulting project, I came across a very interesting problem. It was interesting enough, that I am passing it on to you.
Video – Count Stint for Employees
Consider this Employee Data
- The data represents an employee role for each month, since the employee’s joining date.
- Each row means 1 employee assigned to a certain role for 1 month.
Problem – Count the number of role stints for each employee.
What is a Role Stint – It is an unbroken period of months an employee was set to a particular role.
Let me help you understand better, consider this example. I filtered the data to the employee code = EMPC59769 and I can see that he’s had 5 role stints so far.
Note – A stint is not the unique count of the role 😉
Ideal Output
- Your output should look like this.
- And should be sliceable by any dimension table that connects to the dataset provided.
Rules
- It’ll be ideal if you can solve this with a single measure but if need be, feel free to alter the data model (create helper columns etc..)
- Don’t solve this using Power Query.
- Post your DAX in the comments below. If your output is slightly more complex than a single measure, please drop a link to download your PBI file.
All you Power BI heros out there, get going!
Count Stints – Solution
