Calculate salary running total based on employment start and end date

[Anonymous]

Hi all,

I have this data below and a Date table. Mark was promoted to different roles hence the change in his salary. I'd like to calculate his monthly salary and prorated salary based on his start and end dates

Employee	Start Date	End Date	Annual	Monthly	Increase
Mark Lopez	5/17/2021		1,200,000.00	100,000.00
Mark Lopez	7/1/2021		1,500,000.00	125,000.00	300,000
Mark Lopez	9/1/2021	9/15/2021	2,000,000.00	166,667.00	500,000

My calculations:

Increase =

VAR PreviousDate_ =

CALCULATE (

MAX ( Employee[Start Date] ),

Employee[Start Date] < EARLIER ( Employee[Start Date] ),

ALLEXCEPT ( Employee, Employee[Employee__Id] )

)

VAR PreviousValue_ =

CALCULATE (

DISTINCT ( Employee[Annual] ),

Employee[Start Date] = PreviousDate_,

ALLEXCEPT ( Employee, Employee[Employee__Id] )

)

VAR CurrentValue_ = Employee[Annual]

RETURN

IF (

NOT ISBLANK ( CurrentValue_ ) && NOT ISBLANK ( PreviousValue_ ),

CurrentValue_ - PreviousValue_

)

Original Salary = CALCULATE(SUM(Employee[Annual]), Employee[Increase] =BLANK())

CUMULATIVE =

CALCULATE(([Original Salary]+ SUM(Employee[Increase]))/12,

FILTER(ALLSELECTED('Date'[Date]),

'Date'[Date]<=MAX('Date'[Date])))

Running Total (SALARY)  =

VAR MaxDate =

MAX ( 'Date'[Date] )

RETURN

CALCULATE ([CUMULATIVE],

Employee[Start Date] <= MaxDate,

OR(ISBLANK(Employee[End Date]),

Employee[End Date] > MaxDate)

)

Result:

I computed the prorated salary as

  Days Worked =  DATEDIFF(Employee[Start Date], ENDOFMONTH('Date'[Date].[Date]),DAY) +1

  Prorated Salary =( Days Worked*Monthly)/30

But it did not return what I was looking for

Im looking to have:

Employee	Start Date	Annual Salary	Monthly	Paid
Mark Lopez	5/17/2021	$   1,200,000.00	₱  100,000.00	₱    50,000.00
Mark Lopez	6/1/2021	$   1,200,000.00	₱  100,000.00	₱  100,000.00
Mark Lopez	7/1/2021	$   1,500,000.00	₱  125,000.00	₱  125,000.00
Mark Lopez	8/1/2021	$   1,500,000.00	₱  125,000.00	₱  125,000.00
Mark Lopez	9/1/2021	$   2,000,000.00	₱  166,667.00	₱    83,333.00

Appreciate your help on this one!

[AlexisOlson · Accepted solution]

Getting the number of days employed in a particular month is the hardest part, IMO.

Here's my attempt:

MonthlyPaid =
VAR MonthEnd = MAX ( 'Date'[Date] )
VAR MonthStart = MIN ( 'Date'[Date] )
VAR StartDate = CALCULATE ( MAX ( Employee[Start Date] ), Employee[Start Date] < MonthEnd )
VAR EndDate =   CALCULATE ( MIN ( Employee[End Date] ),   Employee[End Date] > StartDate  )
VAR MonthlySalary =
    CALCULATE (
        SELECTEDVALUE ( Employee[Monthly] ),
        Employee[Start Date] = StartDate
    )
VAR DaysEmployed =
    DATEDIFF (
        MAX ( StartDate, MonthStart ),
        MIN ( EndDate, MonthEnd ),
        DAY
    ) + 1
VAR DaysInMonth = DAY ( MonthEnd )
RETURN
    IF ( DaysEmployed > 0, MonthlySalary * DaysEmployed / DaysInMonth )

[AlexisOlson · Accepted solution]

You might just need to handle null for EndDate. See if it helps to replace

MIN ( EndDate, MonthEnd )

with 

IF ( ISBLANK ( EndDate ), MonthEnd, MIN ( EndDate, MonthEnd ) )

[AlexisOlson · Accepted solution]

Getting the number of days employed in a particular month is the hardest part, IMO.

Here's my attempt:

MonthlyPaid =
VAR MonthEnd = MAX ( 'Date'[Date] )
VAR MonthStart = MIN ( 'Date'[Date] )
VAR StartDate = CALCULATE ( MAX ( Employee[Start Date] ), Employee[Start Date] < MonthEnd )
VAR EndDate =   CALCULATE ( MIN ( Employee[End Date] ),   Employee[End Date] > StartDate  )
VAR MonthlySalary =
    CALCULATE (
        SELECTEDVALUE ( Employee[Monthly] ),
        Employee[Start Date] = StartDate
    )
VAR DaysEmployed =
    DATEDIFF (
        MAX ( StartDate, MonthStart ),
        MIN ( EndDate, MonthEnd ),
        DAY
    ) + 1
VAR DaysInMonth = DAY ( MonthEnd )
RETURN
    IF ( DaysEmployed > 0, MonthlySalary * DaysEmployed / DaysInMonth )

[Anonymous]

Hi @AlexisOlson thank you for taking the time to work on this. I used your formula to recreate the same visual in your solution but the May month is not showing on my end. What am i missing? I used Date table as x-axis

Thanks!

[AlexisOlson]

I'm not sure. Do you have any table relationships doing unintended filtering?

Maybe try debugging the measure by returning each of the VARs until you find the one not working as expected?

[Anonymous]

Hi @AlexisOlson i've found what i was missing! The formula works perfectly for inactive employees. If you would be so kind, how can I make it work for active employees (employees with no employment end dates)?

[AlexisOlson · Accepted solution]

You might just need to handle null for EndDate. See if it helps to replace

MIN ( EndDate, MonthEnd )

with 

IF ( ISBLANK ( EndDate ), MonthEnd, MIN ( EndDate, MonthEnd ) )

[Anonymous]

@AlexisOlson follow up question - (pls excuse my little knowledge in dax)

when selecting 2 or more employees to see the cumulative salaries, why is it showing like this? I would like them to add up. 

[AlexisOlson · Accepted solution]

The measure is looking up a single salary. For multiple employees, you'll probably need to wrap the measure with SUMX to iterate through separate employees.

SumMonthlyPaid =
SUMX ( VALUES ( Employee[Employee_Id] ), [MonthlyPaid] )

[Anonymous]

@AlexisOlson perfect!!! thank you so much! I really appreciate  your help!