Debtor Days using DateTimeDiff

[cparsad001]

Hi Community, I hope you are all well.
I am working on a workflow and I am stuck on the final phase. I am basically trying to calculate Debtor Days using the DateTimeDiff formula. However, I am not sure how to get the correct answer. 

My ultimate objective is to calculate how many days does it take for column Bills to reach each Closing Bal and output it in Debtors day column. The first answer should be around 4 days (10+20+20+30).

I have attached a copy an example of the table I am working with.

[Hammad_Rashid]

Not possible with DateTimeDiff.

[Qiu]

@cparsad001 
Can you show us how you get the 4 days (10+20+20+30) from the data of your snapshot?

[cparsad001]

Thanks. What is the solution then?

[cparsad001]

The logic is as follows: My closing balance as at 15-Jan is 60. In order for me to reach the 60, it takes around 4 days of bills (10+20+20+30).
My closing balance as at 14-Jan was 70, basically it took me 3 days of Bills to meet this closing balance. (20+20+30).
I want the formula to calculate the number of days it took in bills to meet the closing balance everyday.

[CoG]

Thanks for the further explanation. Here is a sample that utilizes a batch macro to accomplish your goal:

Main:

Batch Macro:

[cparsad001]

Thanks @CoG . However, I am unable to import the workflow though. It seems that you are using an updated version of Alteryx.
My version is the 2020.4 one and I cannot update it as it is company regulated. They are yet to release the newer versions.
Is there another way I can access your workflow?

[CoG]

Do you actually get an error or just a warning? Many times, it will open just fine (even in prior versions). Regardless, I tried to make a new file for you. Hopefully, this one works better.

[cparsad001]

Thanks a lot @CoG . It is actually an error but I was able to downgrade the version of your previous workflow so that it opens on my side.

The macro is making wonders! Thanks for this. 
There's still one major thing - I checked my real data and noticed that bills are not always issued everyday as in the sample data.
For instance, I might have issued a bill last Wednesday and a new bill only today, representing a difference of 8 days. However, the macro will ouptut 1 (day) as Debtors Day as I believe it is calculating the same in a "row ID context" rather than checking the date difference when the closing balance is met. Would you know how to resolve this? 

I've attached another sample for better understanding.

[Peachyco · Accepted solution]

Andrew's already close to the mark. Instead of counting rows, though, you...

1. Fetch the earliest/lowest Invoice Date among the relevant rows
2. Attach that date to your target row
3. Use DateTimeDiff to calculate the Debtor Days for that target row

I've attached a package. Here's a sample output: