Running Total Accounts Receivable Aging of Open Invoices DAX

[BlueNote]

How can I create running totals for aging buckets in my model of Accounts Receivable ledger and receipts, in order to show aging "as of" certain dates? Specifically, how can I match the amount in the "0-30 Days RT" measure to the correct amount shown in the "0-30 Days" measure displayed in the red boxes?

In addition, I would like to know how to display the open amount running total aging schedule (0-30 Days RT, 31-45 Days RT, Etc.) by year, month, week, etc., with the aging buckets showing the running total open amount "as of" the date in context. So for example I might put in the GL Date calendar Year as a column and see open amount running total aging as of last day in each year.

I have gotten this far with getting my data out of our system, doing the ETL and am stuck on the DAX particularly the time intelligence and the intermediary virtual tables required to do this. Basically every single Doc|Pay Item needs to be aged and then the open amount added together in the context of a particular year, month, week etc. Hopefully this is something DAX can do quickly but I am open to doing more ETL if neccessary. Was thinking some sort of aggregation table might be helpful, although would be quite huge.

Here is a randomized version of my model with dummy customers (random pronouns and nouns from a dictionary), fake amounts,  etc. The data structure and size is basically the same as the real model. https://drive.google.com/file/d/1iX264djHnp3wW85-nVyQMBytn8zvMpc8/view?usp=share_link

[scee07]

Hi, 
given I have understood the problem correctly. Here is my approach.
Let' assume you have an invoice and payment table

You define a relation between the invoice number and the invoice reference in the payment table

The you get the related invoice date by:

InvoiceDate = RELATED(Invoices[InvoiceDate]).

Delta = CONVERT(Payments[PaymentDate] - Payments[InvoiceDate], INTEGER) gives the delta between invoice and payment date. 

Your bins are custom, so no automatic binning of the day delta:

Day Bins =

SWITCH(

TRUE(),

Payments[Delta] >= 0 && Payments[Delta] <= 30, "0 - 30 days",

Payments[Delta] > 30 && Payments[Delta] <= 90 , "30 - 90 days",

Payments[Delta] > 90, "greater 90 days"

),

or whatever you need as ageing bins.
Then you use the matrix visual as suggested:

Best regards 
Christian

[scee07]

OK I have overread the running total part. Will come back to you.

[scee07]

If nobody else answers this in the meantime, I would have questions to answer this.
a) is the as-of-date supposed to be a slicer in the report, where you set the date?
b) what is the running total in the context? If you set the date where your ageing starts. Then a payment is received or not. 
c) If you do time series for the running total then you still have static table to give you the right amount per date 

So, is it both a start date and then a running total of (open or received payments) in certain time intervals (which might be taken at the end of each interval)?

If somebody has an easy solution, please step in.  I do not see the easy solution at the moment, but upon clarifying the business requirement, this can be setup.

Best regards
Christian

[scee07]

Hi, I have some today and downloaded your model. 
If you are interested, I have the following question:
In the case you want to do the devolopment of ARs let's say by customer and week.  I would assume I need the original AR, which is the original invoice amount and the payments on this invoice (this is your AR receipts).  The AR ledger gives me only a snaphot of the ledger at a certain date. This gives me the static AR overdue view for the snaphot date of the AR ledger. 
So, for everything what you do not have already, one would need the invoices created by customer. Do you agree, or am I missing the scenario?

If the discussion is not meeting your needs, just ignore me 🙂

Best regards 
Christian

[BlueNote]

BTW the original AR amount is the "Gross Amount" column. 

[BlueNote]

Sorry if my question was confusing. Here is an application of what I am looking for

I would want a report like this (just using random data), with a slicer by year. 

[scee07]

Hi, 
I understand your requirement. However I still think that the AR Ledger is not giving you this. The AR Ledger by customer gives you a snapshot at the time you pull it from your system. This is clear if you filter a customer number and one document number. You get exactly one entry. 
So, in  my opinion you need the inititally created invoice amounts with date and due date and the AR receipts (which is already there). As well as some write offs (credits) with date customer and document number. 

Then the entire AR history per document can be derived. 

To pull the ledger - lets say every day-  and append to  a table is no option as you pull a lot of redundant data as a lot of days there is no movement in the ledger for a certain document/ customer combination.

Still not sure if we have the same understading. 

Please let me know, if you see this differently. 

Best regards 
Christian 

[BlueNote]

The AR Ledger is provided for all invoices and the transactional information is provided in the Receipts detail table which has the date of the transaction. I can use this information to get the As of Open balance for any date using the "Open amt. RT" measure. I just need to figure out a way to categorize the open amount rt. measure into the aging buckets (0-30 days, 31-45 days) via DAX.

[scee07]

Ok, if I shall outline this in concrete terms you have to tell me excactly where I find the invoice date the invoice amount for a document/customer combination in your model (I can see that I find the due date in the ledger information)

However, in general terms:

- create a base table with all transactions for a document starting with the invoice created and the invoice amount, then all payments, partial payments,  write offs and credits

- in this table you can do a calculated column which gives you the overdue days at exactly this transaction date
- from this you you can do the column with the ageing bin as described in the begining with the switch statement

- than you define a date table for the relevant period (min date: invoice creation date, max date last transaction date ) dynamically in DAX 
- set up the relation with your transacation base table 

- use the matrix visual to show the running total AR 
- if you want to split the credits define a category column in the base table with a symbol for credits and a symbol for other, or whatever is you need

Obviously it will look different then your excel type matrix above, but contain the same information. 

Best regards 

Christian