DAX Help - Accounts Receivable Aging: calculate monthly closing balance for previous periods

[sajidtp]

Hi,
Newbie here, been trying to solve this for a while - appreciate any help: I have AR transaction table in following format:

Document No	Date	Amount	Balance	Status	Closed ON
Invoice No. 16	31/01/2019	1,906	0	CLOSED	31/03/2019
Invoice No. 18	01/02/2019	316	0	CLOSED	26/02/2019
Invoice No. 22	10/02/2019	10,459	10458	OPEN
Invoice No. 23	10/02/2019	760	0	CLOSED	24/03/2019
Invoice No. 45	06/03/2019	5,144	5143	OPEN
Payment No. 1	23/02/2019	-6,861	0	CLOSED	26/02/2019
Payment No. 3	26/03/2019	-91,221	0	CLOSED	31/03/2019

Table is live connected to Power BI and is constantly updated.  We need to find closing balance of each month as of today (report is always as of today's date) to show trend and for Days Sales Oustanding (DSO) calculation. How can this be achieved?

Result rendered in a graph: it shows total of open debt at end of each month, at that point in time when payment was not received and hence not closed. 

Thank you in advance! I have tried to search for answer in forum and haven't come across any; in case if I have missed please point me to it. Have a great day!

[Ashish_Mathur]

Hi,

Based on the Table that you have shared, please show the exact expected result in a simple Table format.  Once we get the correct numbers in a Table, creating a column chart will not be a problem.

[sajidtp]

Thank you @Ashish_Mathur .  Please consider below table (tweaked earlier table to fit in):

Document No	Date	Amount	Balance	Status	Closed ON
Invoice No. 16	31/01/2019	1,906	0	CLOSED	31/03/2019
Invoice No. 18	01/02/2019	316	0	CLOSED	26/02/2019
Invoice No. 22	10/02/2019	10,459	3914	OPEN
Invoice No. 23	10/02/2019	760	0	CLOSED	24/03/2019
Payment No. 1	23/02/2019	-6,861	0	CLOSED	26/02/2019
Invoice No. 45	06/03/2019	5,144	5143	OPEN
Payment No. 3	26/03/2019	-9,221		OPEN	31/03/2019

Expected results:

Month	Closing Balance	Remark
Jan-19	1906	Invoice No.16 open balance
Feb-19	6580	Payment No.1 offset against Invoice No.18 & partially offset with invoice 22 + new invoice 23 + old invoice 16 (1906 + 316 + 10459 + 760 - 6861)
Mar-19	-2172	Payment No.3 partially offset against Invoice No.45, fully offset 16, add new invoice 45 (1906 + 1 + 5144 - 9221)

Expected outcome in monthly context:

Thank you in advance!

[Ashish_Mathur]

Hi,

What is the relevance of the Balance column?  For Feb, why is invoice 16 not offset before invoice 18?  What is the relevance of the last 2 columns?

[sajidtp]

1. Balance column is amount not offset, or "open" as of today (In our example 26-Mar-2019)
2. For Feb, offsetting is done based on business reasons (hence could be partial or non-chronological). 

3. "Status" is "Closed" when full value of invoice or payment that is offset. Any residual value is indicated as "Open". This status is as of today.

4. Closed ON: This is date of full offset. 

Included an additional column to indicate amounts that are offset. Balance as of 26-Mar-19 is 6390 (Excuse, -2172 was wrong in last table)

Document No	Date	Amount	Offset	Balance	Status	Closed ON
Invoice No. 16	31/01/2019	1,906	1,906	0	CLOSED	31/03/2019
Invoice No. 18	01/02/2019	316	316	0	CLOSED	26/02/2019
Invoice No. 22	10/02/2019	10,459	6,861	3,914	OPEN
Invoice No. 23	10/02/2019	760		0	CLOSED	26/03/2019
Payment No. 1	23/02/2019	-6,861	-6,861	0	CLOSED	26/02/2019
Invoice No. 45	06/03/2019	5,144	1	5143	OPEN
Payment No. 3	26/03/2019	-9,221	-6,554	-2,667	OPEN	31/03/2019
				6390

[Ashish_Mathur]

Hi,

I don't think i can help you with this.  Someone else would pitch in.

[sajidtp]

Thank you @Ashish_Mathur for trying, appreciate it. Hope I get a solution soon... 

[Anonymous]

@sajidtp 

Refer to the table below...

Document No	Date	Amount	Offset	Balance	Status	Closed ON
Invoice No. 16	31/01/2019	1,906	1,906	0	CLOSED	31/03/2019
Invoice No. 18	01/02/2019	316	316	0	CLOSED	26/02/2019
Invoice No. 22	10/02/2019	10,459	6,861	3,914	OPEN
Invoice No. 23	10/02/2019	760		0	CLOSED	26/03/2019
Payment No. 1	23/02/2019	-6,861	-6,861	0	CLOSED	26/02/2019
Invoice No. 45	06/03/2019	5,144	1	5143	OPEN
Payment No. 3	26/03/2019	-9,221	-6,554	-2,667	OPEN	31/03/2019
				6390

Against Invoice No 22, there is a balance of 3914 and offset of 6861. How will you determine if the offset has been done in February or March? Based on that decision, the reported outstanding of Feb & March could vary. I am asking this question because you said it's not FIFO based.

Is it possible for you to include an offset date? To accurately calculate the figures, we need to know when an invoice's outstanding has been offset. 

[Anonymous · Accepted solution]

On the assumption that there is no manual intervention and the outstanding against invoices are knocked-off based on payments on a FIFO basis, the following solution will work.  Go through this and we will fine-tune it once again to suit the requirements.

Table: Transactions

Document No	Date	Amount	Balance	Status	Closed ON
Invoice No. 16	31-01-2019	1,906	0	CLOSED	31-03-2019
Invoice No. 18	01-02-2019	316	0	CLOSED	26-02-2019
Invoice No. 22	10-02-2019	10,459	3914	OPEN
Invoice No. 23	10-02-2019	760	0	CLOSED	24-03-2019
Payment No. 1	23-02-2019	-6,861	0	CLOSED	26-02-2019
Invoice No. 45	06-03-2019	5,144	5143	OPEN
Payment No. 3	26-03-2019	-9,221		OPEN	31-03-2019

Step 1: Add a calendar table.

Calendar = CALENDAR(MIN(Transactions[Date]),MAX(Transactions[Date]))

Step 2: Add 3 calculated columns to the calendar table.

Year = YEAR('Calendar'[Date])
Month = FORMAT('Calendar'[Date],"mmm")
MonthNumber = MONTH('Calendar'[Date]) // for sorting the month names

Step 3: Create four measures

LastDate = MAX('Calendar'[Date])

Credit =
SUMX (
    FILTER (
        Transactions,
        Transactions[Amount] < 0
            && Transactions[Date] <= [LastDate]
    ),
    Transactions[Amount]
)

Debit =
SUMX (
    FILTER (
        Transactions,
        Transactions[Amount] >= 0
            && Transactions[Date] <= [LastDate]
    ),
    Transactions[Amount]
)

Outstanding = [Debit]+[Credit]

Step 4: Add a Matrix Visual to the model.

Add Calendar[Month] on Rows of the Matrix Visual

Add Credit, Debit, Outstanding to the Values filed of the Matrix Visual.

(Remember, you should not have any relationship between the calendar table and the transaction table.)

You will get the following output.

Month	Debit	Credit	Outstanding
Jan	1906	0	1906
Feb	13441	-6861	6580
Mar	18585	-16082	2503
Total	18585	-16082	2503

The debit and credit are the running totals of the debit and credit up to the last day of the month in the current context.

Remember to sort the Month using the MonthNumber field. If you want the year, you may add Calendar[Year] field also to the visual. 

[sajidtp]

@Anonymous what an elegant and unexpected solution! Thank you thank you - made my whole week! Kudos to you and this community, marvelous...

[Anonymous]

Hi,

If you don't want to see Debit and Credit separately in your visuals, then you can directly calculate the outstanding...

Outstanding =
SUMX (
    FILTER (
        Transactions,
         Transactions[Date] <= [LastDate]
    ),
    Transactions[Amount]
)

You can avoid the other two measures that calculate Debit and Credit separately and adding them together to arrive at the Outstanding. Those measures are required only if you want to see the Debit and Credit in the visuals separately. So when you apply this solution to your final model, it's better to reduce the number of measures.