Finding next payment date for a client

Q&A
, , ,
1

i have a table with a list of cases with information on that case for each payment date (duedate). There are only 4 at the moment, however it will be updated to have more. i want to create a separate table which tells me the “applicantid” , and only the next “duedate” (date) they need to pay. applicantid data type is as a string, and looks like this “ab001001”, “ab001002” etc. and the duedate is as a date datatype.

How can i achieve this? it should basically show:
Applicant ID | Next Payment Date |
AB001001 | 15/10/2023 |
AB001002 | 27/10/2023 |
AB001003 | 01/11/2023 |
AB001004 | 09/11/2023 |

2

can you show some sample rows for the input table?

3

Is the date you looking for always the last or max?
Have two functions in mind

  1. maxOver - Amazon QuickSight
  2. lastValue - Amazon QuickSight
4

No its the next date. so if someone has a loan, i want to know when there next duedate is. if someones loan payment duedate is 15/10/2023, for the next 2-3 days i should see for that case is is 15/10/2023. then once its the 16/10/2023, the next duedate for that case should be 15/11/2023.

Does this make sense?

5

How is your base table look like? Applicant ID and Due Date?
And you want to calculate the Payment Date based on what condition?

if “due date” <= today+3 then “due date” else “due date” +1M?

6

I can’t show as is real data, however made a fake table in excel to somewhat replicate. i couldnt be bothered to calculated the interest £ for each one for each month as it will decline.

image
image767×467 8.06 KB

However i basically want to find the next date from today for each case so it looks like this:
image

7

view reply i sent to ersin for better understanding. payment date should be calculated based on next date a payment is due (duedate)

8

Maybe somthing like

lastValue({DueDate}, [{DueDate} ASC], [ApplicantID])