Calculate Total CTC until Employee End Date only

[Anonymous]

Hello all,

I am calculating the CTC of X employees per month. But I'd like to calculate only until their employment end date.

sample Data:

Employee__r.Name	Compensation_Start_Date__c	Compensation End Date	Employment_End_Date__c	Total CTC
Hersheys Adams	6/1/2021		8/17/2021	7,525
Gino Park	7/5/2021		9/2/2021	8,813

I have this formula:

CTC =

var EmpEndDate = CALCULATE(LASTDATE(Compensation[Employee__r.Employment_End_Date__c]), ALL(Compensation))

return

IF(SELECTEDVALUE('Date'[Date]) > EmpEndDate, BLANK(),

CALCULATE([TOTAL CTC],

FILTER(ALLSELECTED('Date'),

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

However, the CTC is showing till end of the year (past their end date)

Date	Employee__r.Name	Compensation_Start_Date__c	Compensation End Date	Employment_End_Date__c	CTC
Jun 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Jul 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Aug 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Sep 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Oct 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Nov 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Dec 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Jul 2021	Gino Park	7/5/2021		9/30/2021	8813
Aug 2021	Gino Park	7/5/2021		9/30/2021	8813
Sep 2021	Gino Park	7/5/2021		9/30/2021	8813
Oct 2021	Gino Park	7/5/2021		9/30/2021	8813
Nov 2021	Gino Park	7/5/2021		9/30/2021	8813
Dec 2021	Gino Park	7/5/2021		9/30/2021	8813

I'm expecting something like 

Date	Employee__r.Name	Compensation_Start_Date__c	Compensation End Date	Employment_End_Date__c	CTC
Jun 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Jul 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Aug 2021	Hersheys Adams	6/1/2021		8/30/2021	7525
Jul 2021	Gino Park	7/5/2021		9/30/2021	8813
Aug 2021	Gino Park	7/5/2021		9/30/2021	8813
Sep 2021	Gino Park	7/5/2021		9/30/2021	8813

Any help is much appreciated!

[wdx223_Daniel · Accepted solution]

IF(MIN('Date'[Date]) > EmpEndDate

[Anonymous]

@wdx223_Daniel Thank you so much!!