How to calculate the number of installments based on installment frequency, start date and end date

[DianaT]

Hi all,

I have a data list that contains the following fields:

Amount	Application	Installment frequency	Installments start date	Installments end date
$400.00	Recurring gift	Yearly	25/10/2020	23/09/2023
$27.78	Recurring gift	Monthly	25/10/2020	25/09/2023
$30.00	Recurring gift	Monthly	25/10/2020	25/09/2023
$2,000.00	Recurring gift	Yearly	25/10/2020	25/09/2022
$55.55	Recurring gift	Monthly	25/10/2020	25/09/2023
$10.00	Recurring gift	Monthly	25/10/2020
$20.00	Recurring gift	Monthly	25/10/2020	25/09/2022
$150.00	Recurring gift	Yearly	25/10/2020
$30.00	Recurring gift	Monthly	25/10/2020	25/09/2023
$15.00	Recurring gift	Monthly	25/10/2020

Is it possible to create a calculated column that reflects the number of installment involved for each of these gifts (either to the end-date or up to 5 years where there is no end-date or the end-date is more than 5 years)?

Ultimately I am trying to use the number of instalment to calculate the total amount of gift committed. 

For example:
- a monthly gift of $30 for three years would look like this = $30 x 36 = $1080
- an annual gift of $300 for three years would look like this = $300 x 3 = $900

Any help will be greatly appreaciated. Thank you.

Diana

[v-shex-msft]

HI @DianaT,

It sounds like a common data analysis requirement across the date range from multiple date fields. You can take a look at the following blog 'start date', 'end date' part if it meets your requirement:

Before You Post, Read This 

In addition, you can also try to create a new table to expand the date range then you can simply calculate the values based on the new table date fields:

Spread revenue across period based on start and end date, slicer and dase this using different dates 
Regards,
Xiaoxin Sheng

[DianaT]

Thank you Xiaoxin. These references are very useful.

[DianaT]

@IreneNordOne, this is really great and very close to what I'm looking for!

At the last part of the DAX, I tried to incorporate an extra condition to specify what happened when end date is blank, like this:

IF ( ISBLANK ( 'Table'[Installments end date] ) ),
SWITCH (
'Table'[Installment frequency],
"Monthly", 60 * 'Table'[Amount],
"Yearly", 5 * 'Table'[Amount]
)
)

Is this do-able? I've been learning DAX for a good couple months now and find incoporating multiple conditions or filters in one DAX very tricky...

Thank you so much for your help.

Cheers,
Diana

PREVIEW

[Anonymous · Accepted solution]

Hi,

You can try this:

New Column =
VAR CountMonthlyInstallments =
DATEDIFF (
'Table'[Installments start date],
'Table'[Installments end date],
MONTH
) + 1
VAR CountYearlyInstallments =
DATEDIFF (
'Table'[Installments start date],
'Table'[Installments end date],
YEAR
)
RETURN
IF (
NOT ( ISBLANK ( 'Table'[Installments end date] ) ),
SWITCH (
'Table'[Installment frequency],
"Monthly", CountMonthlyInstallments * 'Table'[Amount],
"Yearly", CountYearlyInstallments * 'Table'[Amount]
)
)

[amitchandak]

@DianaT , if need to split the data month-wise, refer to the file attached. I have done it from date range to the day, you have to do only once in a month. so add filter [date] =eomonth([date],0)