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-
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
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.