Amount calculation

[andra2]

Hello, 

I want to calculate for each candidate which is the amount only for the first 8 months of contract, no matter if he has only one or more placements. For instance, I have this sample of data for which I will expect to receive the below desired result:

Candidate ID	Placement	Amount	Start Date	End Date	Comments	Desired Result
x	1	10000	1/1/2020	10/31/2020	no new placement, more than 8 months	8000 - for this case, we need to calculate only 8 months from the amount
y	2	400	1/1/2020	3/31/2020	current placement less than 8 months	400 - placement 1, for this case we take into consideration the entire amount as 8 months did not passed and is the first placement
y	2	12000	7/1/2020	12/31/2020	second placement, reaches 8 months with the previous placement	800 - 2nd placement, we need to take into consideration only 4 months from this placement  as we had in the first placement 4 months
z	3	400	1/1/2020	3/31/2020	current placement less than 8 months	400 - placement 1, for this case we take into consideration the entire amount as 8 months did not passed and is the first placement
z	3	500	7/1/2020	9/30/2020	second placement less than 8 months	500 - 2nd placement, we need to take into consideration the entire amount as in the placement we have only 3 months of contract and the overall 8 months are not yet fulfilled
z	3	10000	1/1/2022	5/31/2022	thrid placement, fulfills the 8 months	400 - 3rd placement, we need to calculate the amount only for the remaining months: 2 months =  8 months total months - 3 from the first placement - 3 from the second placement

Candidate x	8000
Candidate y	1200
Candidate z	13000

Can you, please, help me with the calculations I need to deploy for it?

Many thanks!

[amitchandak · Accepted solution]

@andra2 , We need the following columns

Months  = datediff([Start Date], [End Date], Month)+1

Monthly Amount = [Amount]/Months

Previous Month = sumx(Filter(Table,[Candidate ID] =earlier([Candidate ID]) && [Start Date] < earlier([Start Date] )) ,[Months])

Month to be added = Switch( True() ,

coalesce([Previous Month],0) >=8 , 0 ,

coalesce([Previous Month],0)=0 &&  [Month] <= 8 , [Month] ,

coalesce([Previous Month],0)=0 &&  [Month] >= 8 , 8 ,

[Previous Month] +[Month]<=8 && [Month] <= 8, [Month] ,

8-[Previous Month]

)

New Amount = [Month to be added] * [Monthly Amount]

[andra2]

Thank you very much, it worked 🙂