Calculate next recurrence of a date based on interval

[i_piyush]

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

1. Start Date - when was the subscription started
2. Category - defines what the interval is; possible values - Month, Year, Day
3. 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.

[mdevaney]

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

[i_piyush]

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

[mdevaney]

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

[ekarim2020]

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

[i_piyush]

---

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

[i_piyush]

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.

[ekarim2020]

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

[mdevaney]

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