How to select mothly payments with only one entry in a table

[talex]

Hello Guys,

I want to have a table with something like:

Start contract Date I Name I Mothly pament amount I Date until contract is valid I etc. I ...

When using the slicer selecting 01.01.2022 - 30.06.22 for instance I want to get 6x the amount of the payment as a result if the contract is valid.

For me it sounds quite complicated as I have to check if Start contract date and Date until contract is valid and both fits the date criteria and then calculate the amount for each month. Contract can also start on 15. of  a moth, therefore I should calculate just 50% in the first month of the monthly payment and when selecting 01.06.2022 - 30.06.2022 I would expect 1x the amount of payment, even that Start contract date is not between the slicer selection in this case...

Is DATESBETWEEN or DATESINPERIOD a solution? Or is there an easy way to do this, do not really know how to start the selection...Who can help please?

[talex]

data slicer = 01.01.2022 - 30.06.2022 should return only sum (1+2+...+12)*1.

But just because your "date until contract is valid" ends each month.

Normaly this is never the case. A rental contract for an apartment does not end at the end of a starting month. At least 03 months!

data slicer = 01.06.2022 - 30.06.2022 should return only sum (11+12)*1 in this case.

Again because your "date until contract is valid" ends each month.

So lets assume Line 1 (valid until 06/15/2022) and Line 2 (valid until 12/31/2022):

data slicer = 01.01.2022 - 30.06.2022 should return only sum (1)*5,5 + sum(2)*6 + sum(11+12)*1.

data slicer = 01.06.2022 - 30.06.2022 should return sum (1*0,5+2+11+12)*1 in this case.

Suppose Line 2 starts on 15.01.2022 and end on 31.12.2022, slicer 01.01.2022 - 30.06.2022 should also calculate than 5.5 for line 2 for instance...

[v-xiaotang]

Hi @talex 

Thanks for reaching out to us.

>> The known conditions are

1.  date slicer= 01.01.2022 - 30.06.22, measure returns 6x the amount of the payment if the contract is valid.

2. Contract can also start on 15. of  a moth, therefore I should calculate just 50% in the first month of the monthly payment and when selecting 01.06.2022 - 30.06.2022 I would expect 1x the amount of payment, even that Start contract date is not between the slicer selection in this case

I create a sample based on it, could you provide the expected output of the sample? Thanks.

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.