March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Register NowGet certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
Hello,
I am trying to create a summary table and could use some help! I am trying to calculate the amount of time an employee is in a particular pay plan (salary). For a data source, I am using a change log from an employee database. For privacy purposes, I have modified the data to cleanse it. However, below are some examples of what I am working with and what I am trying to accomplish.
Scenario 1:
ChangeRequestID | EmployeeID | PayPlanID | Date | JobTitleID |
8886 | 400 | 12 | 12/20/2020 | 19 |
6360 | 400 | 11 | 12/22/2019 | 19 |
3713 | 400 | 9 | 5/27/2018 | 19 |
3163 | 400 | 8 | 2/4/2018 | 6 |
1972 | 400 | 7 | 2/5/2017 | 6 |
In this scenario, the employee has one record for each promotion, or move into the next pay plan. The outcome I am trying to get in this scenario is as follows:
PayPlanID | Duration (days) |
12 | 709 |
11 | 364 |
9 | 574 |
8 | 112 |
7 | 364 |
I am wanting the duration column to represent the number of days the employee was in that pay plan. It's calculated by looking at the difference from that record until the next record, with the exception of the most recent record, which uses today's date for the calculation (since the employee is still in that pay plan).
Senario 2:
ChangeRequestID | EmployeeID | PayPlanID | Date | JobTitleID |
13964 | 693 | 9 | 10/10/2021 | 4 |
12532 | 693 | 7 | 8/15/2021 | 5 |
7547 | 693 | 7 | 9/13/2020 | 6 |
6904 | 693 | 8 | 3/29/2020 | 4 |
5246 | 693 | 7 | 3/31/2019 | 9 |
In this scenario, the employee's play plan does not consistently increase. They took another job which had a lesser pay, and then eventually worked their way back up. In this scenario, I am looking for the same output as in scenario one. Below is the expected outcome:
PayPlanID | Duration (days) |
9 | 415 |
8 | 168 |
7 | 756 |
I tried creating a table using SUMMARIZE(), grouping by EmployeeID and again by PayPlanID, however I wasn't sure what expressions I could use to calculate the duration in days. It would be much easier if I had a start & end date in the record, but I do not. Only the date the change was submitted.
I would really appreciate any help or guidance on this!
Thanks!
Chris
Solved! Go to Solution.
You could create an end date column like
End Date =
VAR StartDate = 'Table'[Start Date]
VAR EmpID = 'Table'[EmployeeID]
VAR EndDate = COALESCE(
CALCULATE(
MIN('Table'[Start Date]),
REMOVEFILTERS('Table'),
'Table'[Start Date] > StartDate,
'Table'[EmployeeID] = EmpID
),
TODAY()
)
RETURN EndDate
and then create a measure for the number of days in the pay plan
Days in Pay Plan = SUMX( 'Table', DATEDIFF( 'Table'[Start Date], 'Table'[End Date], DAY ) )
You could create an end date column like
End Date =
VAR StartDate = 'Table'[Start Date]
VAR EmpID = 'Table'[EmployeeID]
VAR EndDate = COALESCE(
CALCULATE(
MIN('Table'[Start Date]),
REMOVEFILTERS('Table'),
'Table'[Start Date] > StartDate,
'Table'[EmployeeID] = EmpID
),
TODAY()
)
RETURN EndDate
and then create a measure for the number of days in the pay plan
Days in Pay Plan = SUMX( 'Table', DATEDIFF( 'Table'[Start Date], 'Table'[End Date], DAY ) )
John, A sincere thank you! This works flawlessly and is exactly what I was looking for. I am going to read through it a few more times today and try to understand it a little better, but I think I understand it. I just would have never thought through that myself. I really appreciate it!
Chris
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
Check out the November 2024 Power BI update to learn about new features.
User | Count |
---|---|
29 | |
20 | |
15 | |
14 | |
10 |
User | Count |
---|---|
62 | |
25 | |
24 | |
22 | |
16 |