Forum Discussion

wvpowerbi's avatar
wvpowerbi
New Member
3 years ago
Solved

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 ...
SUMMARIZE
Summary
Summary Tables
  • johnt75's avatar
    johnt75
    3 years ago

    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 ) )
johnt75's avatar
johnt75
Super User
3 years ago

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 ) )
wvpowerbi's avatar
wvpowerbi
New Member
3 years ago

 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