Next Payment Date Based on Interval and the start date of the payments

(0) Share

Report

Posted on by CU16090615-0

144

Hi there

I hope someone can help me please.

I've created a form below screenshot

In the form there is a 'Date of first payment' and then the 'Cleared by' (which is a date) is a calculated field based on the 'Date of first payment', 'arrangement amount' and the 'Agreement Payment Value'.

What I need to try and calculate is the 'date of next payment', which will be based on 'Date of first payment' and the Agreement Frequency' and using todays date.

So if the start date was 28/2/2023 and they agreed to pay every 2 weeks, the next pay date would show as 25/4/2023. Same if they chose to pay weekly, the next payment date would be 18/4/2023 (if starting 28/2/2023) . Now if they chose to pay monthly (same start date) then the next payment date would be 28/4/2023

Does that make sense. So whatever todays date is it would need to show the next payment date after today

Does anyone know how to do this, and is it actually possible.

Thanks for your help

Kind regards

Karen

Categories:

Building Power Apps

I have the same question (0)

All responses (11)

Answers (0)

CU16090615-0 144 on at

Like (0)

Report
Copy link

Link copied!

Just to add the payment frequencies are
Weekly
2 Weekly
4 weekly
Monthly

Thank you 🙂
Karen

Was this reply helpful? Yes No
turnerj3 2,039 on at

Like (0)

Report
Copy link

Link copied!

@KarenF ,
Add a text label and put this if statement in for text to test out how this works.
You will have to change the Dropdown to yours, Now() to your date field, and you can add another statement on the end for another logical test. Here is a link for reference as well.

DateAdd, DateDiff, and TimeZoneOffset functions in Power Apps - Power Platform | Microsoft Learn

If(Dropdown3.Selected.Value = "Weekly", DateAdd(Now(),7,TimeUnit.Days), Dropdown3.Selected.Value = "Bimonthly", DateAdd(Now(),14,TimeUnit.Days),Dropdown3.Selected.Value = "Monthly", DateAdd(Now(),1,TimeUnit.Months))

Was this reply helpful? Yes No
CU16090615-0 144 on at

Like (0)

Report
Copy link

Link copied!

Hi @turnerj3
Thanks for getting back to me so quickly, really appreciate it 😊.
I'll give it a whirl and let you know.
Kind regards
Karen

Was this reply helpful? Yes No
CU16090615-0 144 on at

Like (0)

Report
Copy link

Link copied!

Hi @turnerj3
It does work to a certain degree. The issue is that if the start date is in the past then gives the next date in the past, I need the date to be the next payment date equal to / after today. Is that possible. tried fixing myself but cant seem to get it to work. 🙄
Thanks again
Kind regards
Karen

Was this reply helpful? Yes No
turnerj3 2,039 on at

Like (0)

Report
Copy link

Link copied!

@KarenF ,
If it is in the past could we calculate the new date based on todays date plus the frequency value?

Was this reply helpful? Yes No
CU16090615-0 144 on at

Like (0)

Report
Copy link

Link copied!

Hi @turnerj3
Yep we could do that but the next payment date needs to be based on the start date and then frequency after that, so its not that simple just using today. The next payment date cant fall out of sequence with the frequency from the start date. Make sense 🙂

Thanks again
Kind regards
Karen

Was this reply helpful? Yes No
turnerj3 2,039 on at

Like (0)

Report
Copy link

Link copied!

@KarenF ,
Just to update. This may be above my paygrade😁. Yes it makes sense. Here is some code that works on a button click, but it isn't practical because no telling how many if statements you need to put in depending how far back.
We need to now figure out how to add the if to a timer loop until the varDate>Today()
If(DatePicker1.SelectedDate<Today(),Set(varDate,DateAdd(DatePicker1.SelectedDate,7,TimeUnit.Days)));
(varDate<=Today(),Set(varDate, DateAdd(varDate,7,TimeUnit.Days)));
If(varDate<=Today(),Set(varDate, DateAdd(varDate,7,TimeUnit.Days)));
If(varDate<=Today(),Set(varDate, DateAdd(varDate,7,TimeUnit.Days)));
If(varDate<=Today(),Set(varDate, DateAdd(varDate,7,TimeUnit.Days)));
If(varDate<=Today(),Set(varDate, DateAdd(varDate,7,TimeUnit.Days)));
If(varDate<=Today(),Set(varDate, DateAdd(varDate,7,TimeUnit.Days)));
If(varDate<=Today(),Set(varDate, DateAdd(varDate,7,TimeUnit.Days)));

Was this reply helpful? Yes No
CU16090615-0 144 on at

Like (0)

Report
Copy link

Link copied!

Hi @turnerj3
LOL definitely above my pay grade.
Think I need coffee to look at this. 😃
Again, really appreciate your help. 🙂
Kind regards
Karen

Was this reply helpful? Yes No
turnerj3 2,039 on at

Like (0)

Report
Copy link

Link copied!

@KarenF ,
I had some coffee as well! Here is what I have come up with. You will probably have to tweak it a little.
I have timer set to autostart. The OnStart value is this.

If(Dropdown3.Selected.Value = "Weekly", Set(varDate,(DateAdd(varDate,7,TimeUnit.Days))), Dropdown3.Selected.Value = "Bimonthly", Set(varDate,(DateAdd(varDate,14,TimeUnit.Days))),Dropdown3.Selected.Value = "Monthly", Set(varDate,(DateAdd(varDate,1,TimeUnit.Months))))

The Repeat Value is:
varDate<=Today()

Im using a DatePicker OnChange event to Set the varDate variable initially:
Set(varDate,DatePicker1.SelectedDate)
You would probably want to set the variable initially onStart of the form or something.
You would also want your Date of Next Payment set to varDate.
Here is picture of my timer:

Was this reply helpful? Yes No
CU16090615-0 144 on at

Like (1)

Report
Copy link

Link copied!

Hi @turnerj3

Thanks so much for looking in to this. I couldnt get quite it to work.However i've come up with the following which seems to do the trick

If('Agreement Frequency_DataCard2_1'.Update = "Weekly", DateAdd(Now(),7,TimeUnit.Days)-Mod(DateDiff('Date of First Payment_DataCard1_2'.Update,Today()),7),
'Agreement Frequency_DataCard2_1'.Update = "2 Weekly", DateAdd(Now(),14,TimeUnit.Days)-Mod(DateDiff('Date of First Payment_DataCard1_2'.Update,Today()),14),
'Agreement Frequency_DataCard2_1'.Update= "4 Weekly", DateAdd(Now(),28,TimeUnit.Days)-Mod(DateDiff('Date of First Payment_DataCard1_2'.Update,Today()),28),
'Agreement Frequency_DataCard2_1'.Update = "Monthly", DateAdd(Now(),31,TimeUnit.Days)-Mod(DateDiff('Date of First Payment_DataCard1_2'.Update,Today()),31))

Need to do a bit more testing but so far so good.

Oh and I could only do this with copious cups of coffee LOL

Kind regards
Karen

Was this reply helpful? Yes No