Set Analysis based on date range

[sagarjagga]

Hi All,

I have a requirement where i need to calculate the sum amount in an interval and the interval will be different for each id.

For example - i have below 2 tables in my data model-

ID	Start date
1	1/1/2019
2	1/10/2019
3	4/5/2019

ID	Amount	Date
1	10	2/2/2019
1	100	10/14/2019
2	110	3/2/2019
2	5	3/3/2020
2	15	4/3/2019
3	20	4/10/2019
3	30	4/13/2019
3	40	4/20/2019

So i need to find out the sum for each id where Date >= StartDate and Date <= AddMonths(Addmonths(StartDate),6).

Since we have different start date for each id so not able to expand the StartDate directly in where condition.

Expected output is

ID	Amount
1	10(10+0)
2	125(110+15)
3	90(20+30+40)

For now I have used below expression with if condition -

Aggr(Sum(if(Date>= Min(StartDate) and Date <= AddMonths(Min(Date),6),Amount),ID)

Can you please suggest me some better solution. I need to do using set expression.