cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

New Member

## Help with creating a summary table

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

1 ACCEPTED SOLUTION
Super User

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 ) )``
2 REPLIES 2
Super User

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 ) )``
New Member

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

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.