cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
wvpowerbi
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: 

ChangeRequestIDEmployeeIDPayPlanIDDateJobTitleID

8886

4001212/20/202019
63604001112/22/201919
371340095/27/201819
316340082/4/20186
197240072/5/20176

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:

PayPlanIDDuration (days)
12709
11364
9574
8112
7364

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:

ChangeRequestIDEmployeeIDPayPlanIDDateJobTitleID
13964693910/10/20214
1253269378/15/20215
754769379/13/20206
690469383/29/20204
524669373/31/20199

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:

PayPlanIDDuration (days)
9415
8168
7756

 

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

View solution in original post

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

 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

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors