Marking transactions as settle in the order they were created

[Yogesh_laddha]

So here’s a tricky one.

I have a table which records sales transactions. If the transaction type is Credit, there is a date(30 days from created date) before which the credit amount should be paid.

There is another table which records all the cash collected. The cash collected is not recorded against any particular transaction in the Sales transaction table, but against the Agent who made the transaction.

So, suppose, if there are 2 transactions by Agent A in the Sales table, 1) Credit amount = 10000 2) Credit amount = 20000.
Cash collected by Agent A before Credit limit date is 20000, I want to mark the transaction as settled in the sales transaction table in the order they were created. So, the 1st transaction in the Sales tab would be completely settled (20000-10000=+10000) and 2nd Transaction would be partially settled. (+10000-20000=-10000).

How do I achieve this?

I hope I explained the issue properly. Let me know if it needs more details.

I have been stuck on this for a while and any help would be appreciated.

Thanks.

[Suvrutt_Gurjar · Accepted solution]

Hi @Yogesh_laddha,

Request you to go through below.I have tried to put together a small test app to test the functionality you mentioned based on my understanding. Hope this helps.

Please take a look at below image of table view of SalesCredit Table in the test app.

Basically there are three VCs in the SalesCredit table with expressions as

1. Column titled CreditSum
   SUM(SELECT(SalesCredit[Amount],AND([Agent]=[_THISROW].[Agent],[Date]<=[_THISROW].[Date])))
   Display name of this column is “Credit Taken” in the above image
2. Column titled CashSum

SUM(SELECT(CashCollected[Amount],[Agent]=[_THISROW].[Agent]))

here CashCollected is the second table but the VC column is in SalesCredit table

3. Column titled Status

IF([CashSum]>=[CreditSum],“Settled”,“Outstanding”)

A limitation is all above is achieved with VCs and could be limiting to keep persistent record?

Hope this helps in some way.

[Yogesh_laddha]

Hi @Suvrutt_Gurjar

Thank you for your detailed explanation.
This is perfect and exactly what I was hoping to accomplish.

Thanks a lot. I really appreciate it.

I apologise for the delayed response. Got stuck with something else.

Thanks.

[Suvrutt_Gurjar]

Hi @Yogesh_laddha,

You are welcome. Good to know the approach was useful. All the very best.