Report to show number of days between latest transaction of account

[Kryjathic]

Hello,

I'm a beginner and need help in designing a workflow.  Basically I need some sort of aging report based on 2 files.  (1) a report of existing clients with their latest transaction date before the current month (eg. March 2020), and (2) transactions for the current month. 

Bottom line is I need a report (see result report below) that

(a) Find the latest transaction date for the month (eg. March 15, 2020), this date will be the "End Date" column, and pick all clients and amounts on that date.

(b) Find the latest transaction date before the "End Date" for each client, this will be the new "Start Date" for each client. (If no previous transaction before "End Date", then "Start Date will be same date as "End Date").

(c) Create a column for the number of days between "Start Date" and "End Date". 

(File 1) Latest transaction before March 2020

Start Date	Client
3/15/2019	Client A
1/1/2020	Client B
2/15/2020	Client C

(File 2) March 2020 transactions

March Dates	Client	Amount
3/1/2020	Client C	100
3/1/2020	Client D	150
3/5/2020	Client C	200
3/15/2020	Client A	300
3/15/2020	Client C	400
3/15/2020	Client D	500
3/15/2020	Client E	600

Result report

Start Date	End Date	Client	Amount	Days
3/15/2019	3/15/2020	Client A	300	366
3/5/2020	3/15/2020	Client C	400	10
3/1/2020	3/15/2020	Client D	500	14
3/15/2020	3/15/2020	Client E	600	0

Thank you,

[echuong1]

Can you check some of the dates in your result table? If I understand your requirements correctly, I'm not sure if some of them are correct. 

I mocked up a workflow based on my understanding. Let me know if this works!

[Kryjathic]

Thanks @echuong1, it's not quite the perfect result but I guess you're workflow is on the right track just needs a little tweaking.  I'll also try to play around this workflow hopefully find the right tools and formulas.

The only error were on:

Client C - your "Previous Transaction Date" was Feb 15 2020 while it should be Mar 5 2020.

Client D - your "Previous Transaction Date" was Mar 15 2020 while it should be Mar 1 2020.

[Kryjathic · Accepted solution]

Solved !! I figured it out.  Thanks @echuong1 for providing the initial workflow for me to tweak.