Unanswered

Calculate next recurrence of a date based on interval

(0) Share

Report

Posted on by Piyush Paliwal

Business Context and Problem statement

I am building a simple canvas app which tracks all my recurring payments. The app would send out a notification when the next recurring date arrives. The problem is to calculate the next due date.

Current Setup

Canvas app has a form component backed by a SharePoint list. There are three parameters that I accept in the configuration

Start Date - when was the subscription started
Category - defines what the interval is; possible values - Month, Year, Day
Interval - the actual recurrence interval; eg - every 1 Month or 2 Year or 10 Day, etc.

I am unfortunately having a hard time wrapping my head around it. So far, following is the code that I have in place to calculate next due date. This obviously does not work as expected.

If(
 And(
 !IsBlank(StartDate),
 !IsBlank(Frequency),
 !IsBlank(Interval)
 ),
 Switch(
 Frequency,
 "Month",
 DateAdd(
 StartDate,
 RoundUp(
 Mod(
 If(
 DateDiff(StartDate,Today(),Months) > 0,
 DateDiff(StartDate,Today(),Months),
 1
 )
 ,12
 ) / Interval,
 0
 ) * Interval,
 Months
 )
 )
)

Any pointers would be greatly appreciated.

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (0)

mdevaney 29,989 Moderator on at

Like (1)

Report

@i_piyush
In my opinion, you should get rid of the frequency dropdown. It won't make sense to your users. Instead, do something like "Monthly", "Bi-Monthly", "Weekly", "Bi-Weekly". Side-bonus is it will be much easier for you to code.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Piyush Paliwal 75 on at

Like (0)

Report

You are right Matthew. As a last resort, I have thought about that option as its the next best thing. However, I am trying to make this as configurable as possible.

Given the current state, do you have any recommendations? I think am close but just can't figure out how to get this to finish line. 😊

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report

@i_piyush

What you are attempting with frequency is just not logical and that’s why you are having problems figuring it out. You need to abandon this idea and define fixed options for monthly, bi-monthly, weekly, daily, etc. Your users will not understand frequency as you have currently designed it.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No
Ellis Karim 11,681 Super User 2025 Season 2 on at

Like (0)

Report
I'm not entirely clear on the issue, but this may give you some guidance or ideas:

Set( varDue, With( {input: ddFrequencyCategory.SelectedText.Value}, If( input = "Day", DateAdd( DatePicker1.SelectedDate, Value(ddFrequency.SelectedText.Value), Days ), input = "Month", DateAdd( DatePicker1.SelectedDate, Value(ddFrequency.SelectedText.Value), Months ), input = "Year", DateAdd( DatePicker1.SelectedDate, Value(ddFrequency.SelectedText.Value), Years ) ) ) )

Label Next Date = varDue

The values in the Frequency drop-down changes depending on whether Month, Year or Day is selected:

You will need to update varDue during OnStart to initially display the Next Date.

And each time Due date, Frequency Category or Frequency is changed, varDue will need to be refreshed (OnChange trigger) again.

You could replicate the interfaces you find for scheduling tasks or backups. Users may find that easier to use (more work!):
Regards,

Ellis

Was this reply helpful? Yes No
Piyush Paliwal 75 on at

Like (0)

Report

@mdevaney wrote:
Your users will not understand frequency as you have currently designed it.
Hi @mdevaney, That's right. The screenshot is probably the earliest version of the prototype, I see why it's confusing 😁. The most recent prototype is below, does this help clear up the confusion?
Parallelly, as you recommended, I am also looking at a possibility of just leaving it at monthly, weekly, daily, yearly, etc.

PS: This is just a hobby project I am working on, to iron canvas app skillset, nothing mission critical, but am scratching my head as to why can't this thing work. 😉

Was this reply helpful? Yes No
Piyush Paliwal 75 on at

Like (0)

Report

Thanks for the detailed explanation @ekarim2020. This is similar to what I have currently, however, it would fail if the base date (varDue) is way back in the past. For instance, you took the example of adding 7 months to 1/1/2021, what if the date was say 15/3/2020, the formula would return me a date which is in the past.

To get around with this situation, we will have to do a Mod before adding any number of days/months to the current date. And that's where am stuck at. The output always has to be the earliest next date in the future following the interval.

Was this reply helpful? Yes No
Ellis Karim 11,681 Super User 2025 Season 2 on at

Like (0)

Report

I think you should look at adding Form Validation to some of the fields:
Example:

Also, the Frequency category and Frequency field can be "disabled" (or hidden) unless a valid due date has been entered.

Please see this excellent tutorial Power Apps form data validation tutorial by Rezza Dorrani which covers all the above and more.

Ellis

Was this reply helpful? Yes No
mdevaney 29,989 Moderator on at

Like (0)

Report

@i_piyush

Use the DateAdd function to find the next date with those parameters.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

Was this reply helpful? Yes No