Applying receipts to outstanding Balances

Hi everyone,

Below is a sample of the scenario I would like to build and need your support. The story goes like this:

I have got all of the Sales Invoices (Debit) and Sales Receipts (Credit). The first answer that I need is easy. I would like to know what is each customer’s total outstanding balance. I have got that by simply saying Debit minus Credit. Given that I have got a list of customers, I can see what the outstanding per customer is.

However, here is the challenge which I dont know how to solve. In the data, there are customers who have go more than one sales invoice and/or receipts. As such, I would like to be able to breakdown the Total Outstanding Balance and see which specific invoice is paid and which one is still outstanding.

I would like to apply the receipt to the oldest invoice first and then to the second oldest, etc.

So, in order to see that, I would like a column that tell me if an invoice is “Paid”, “Part-Paid” or “Outstanding” and ideally, another column that shows me the outstanding balance per invoice.

I would be grateful for any assistance on this.

Here is the file for your reference. Sample.pbix (1.0 MB)

Hello @Jawed,

Maybe you can use a measure like this:

Test D =
SWITCH (
    TRUE ();
    [Outstanding Balance] < 0; "Outstanding";
    [Outstanding Balance] > 0
        && [Total Debits] > 0
        && [Total Credits] = 0; "Paid";
    [Total Debits] > 0
        && [Total Credits] > 0; "Part-Paid"
)

After that you can make a hierarchy on the column ‘Data’[Customer] and add the column ‘Data’[Name] to that hierarchy. Finally you can place the hierarchy and the measures in a Matrix visual. I have attached a PBIX-file. Hope it works.

Daniel

Sample eDNA.pbix (1.1 MB)

1 Like

Hi @uriah1977 and thank you for giving it a go. Apologies if my explanation was not clear enough. The Test D column currently shows the Debit column (invoiced amounts) as paid and the Credit column (received amount) as outstanding.

Let me create a small example of what the outcome should ideally be:

1/Jan/2019 Invoice issued (debit) 200
1/Feb/2019 Invoice issued 400
1/Mar/2019 Payment Received 350

In this example, the first invoice’s outstanding balance (I believe this needs to be a new calculated column) should be zero and status should show as “paid”. The second invoice outstanding balance should be 250 and the status should say “part-paid”.

Due to the huge volume, I would like to be able to filter out the paid invoices and only show the outstanding or part paid invoices in a table.

Hello @Jawed It’s all my mistake. I have turned it around. :smirk:

Daniel

Hi @uriah1977. Have you uploaded the file again?

Hi @Jawed I can do that later this day

Daniel

Hi @Jawed. I have updated the measure and also added some logic to show the status only on Customer Level when using a matrix visual.

Test D =
VAR show =
SWITCH (
TRUE ();
[Outstanding Balance] = 0; “Paid”;
[Outstanding Balance] > 0
&& [Total Debits] > 0
&& [Total Credits] = 0; “Outstanding”;
[Total Debits] > 0
&& [Total Credits] > 0; “Part-Paid”
)
RETURN
IF ( ISINSCOPE ( Data[Name] ); BLANK (); show )

I hope it’s a start.

Daniel

Sample eDNA.pbix (1.2 MB)

Hi @Jawed,

I realized there was still a loose end because you want to filter on status and I can’t see a way to do that by using measures. Maybe there are Enterprise DNA members who can do that with measures, but I can’t. So I want to provide an alternative approach.

You can create a calculated column in table ‘Data’ :

Outstanding BAL =
Data[Debit] - Data[Credit]

After that you can create a new table ‘Status’:

Status =
SUMMARIZE (
Data;
Data[Customer];
“Debit”; SUM ( Data[Debit] );
“Credit”; SUM ( Data[Credit] );
“OB”; SUM ( Data[Outstanding BAL] )
)

Then you can create a new column in table ‘Status’ :

Status =
SWITCH (
TRUE ();
‘Status’[OB] > 0
&& ‘Status’[Credit] = 0; “Outstanding”;
‘Status’[OB] > 0
&& ‘Status’[Credit] > 0; “Part-Paid”;
‘Status’[OB] = 0
&& ‘Status’[Credit] <= ‘Status’[Debit]; “Paid”;
‘Status’[OB] < 0; “Surplus”
)

Next you make a relation between the ‘Data’ table and the ‘Status’ table.

Now you can use column Status from table ‘Status’ as a filter.

I hope it’s useful. There is another bix file attached.

Daniel

Sample eDNA 2.pbix (1.2 MB)

1 Like

Hi @Jawed

I might be completely wrong in my understanding but I did spent hell lot of time to reach till this point :):sweat_smile:

I have created few measures to perform calculations primarily to explain my code as we can consolidate into less also by using Variables.
As there are multiple measures, I have not captured them here.

OK, my Solution goes like below.

In above Screenshot, the measures are as below.

Test C = Sum of all Credits Transactions for a Customer
Test D = Sum of all Debits Transactions for a Customer till that Invoice.
Test O = Outstanding amount. It will show value as ‘0’ if Test D is less than Test C i.e. that Debit is paid else Test D - Test C

Below are Created for Part Paid transactions.

Test LD = Total of Debits till previous debit Invoice
Test LO = Total of Outstanding till previous debit Invoice

Finally TestOText to display Text based on Outstanding value

  1. If Test O = 0, “Paid”
  2. If Test O > 0 and Test LO > 0, “Outstanding” else Part Paid

This will work for both Table and Matrix. To Filter Visual, you can apply filter on TestOText

Same functionality can be achieved using Calculated Columns also (my initial approach) but that wasn’t looking much effective.

If this is not what are you looking for, please provide an example by taking 1-2 customers from current data as that will provide more clarity. Attached PBIX

Sample.pbix (2.0 MB)

Thanks
Ankit Jain

1 Like

Dear @uriah1977 and @ankit. Thank you so much for spending time to look at my case.

Ankit, I think you are close to solving the challenge. In order to better explain the situation, I have used one customer and explained what should ideally happen. Attached is the spreadsheet that should give you the commentary.

For me, my main focus is on the invoices issued (debit column) and to see whether an invoice is still outstanding, partly-paid or paid in full. I will not be displaying the payment receipts (credit column) on the same table. So, the focus is two-fold. First, at a summary level by customers. We want to know if a customer has any outstanding balance. If so, how much is it? At this level, we are not concerned about the breakdown of that balance. Simply, stating whether a customer has an outstanding balance and if so, how much it is, is more than enough.

Secondly, it is at invoice (credit) level. This is the detailed level and we would like to see invoice by invoice and assess if it is Paid, part-paid or outstanding.

In both levels, we would like to be able to set a filter whereby we will filter out all the zero balance customers (at first level) and all the paid invoices (at detailed level).

When I tried your sample data, I wasn’t able to use it as a filter.

I hope this file gives you a better idea of what I am trying to achieve.

Scenario explanation.xlsx (11.3 KB)

Hi. I would be grateful if you had a look at this and see if you can help us. Thank you, in advance.

Hi Jawed,

I have had a look at you excel scenario and what the other guys have done to date.

I have managed to get close to what you had on your excel scenario the only thing hindering me is that there is multiple postings on the same date.

So if any one can work out how to do a cumulative total even when the date is repeated i think you should have what you want.

Regards,
H

Sample eDNA 2 ha.pbix (1.2 MB)

Thank you, @haroonali1000. Whilst the dates are certainly repetitive, the customers arent so it if it is possible to combine the date filtered by customer, that might work. I am just trying to explain it as my DAX is not up to that level but I do have an idea of how it should work :slight_smile:

Hi @Jawed,

This is really a tough cookie I think… But I want to try one final approach before I surrender :confused:

If you go to Power Query you can make a conditional column:

You’ll get this custom column

Then you can Fill down like this:

After that you can Close & Apply

Than you can create a Matrix and a slicer on the customer

I have attached a pbix file based on your excel example because in the Power Query Editor I had of course no access to you Data table.

Hope it works…

Daniel
PQ.pbix (65.4 KB)

1 Like

Hi @Jawed, we’ve noticed that no response has been received from you since December 06, 2019. We just want to check if you still need further help on this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arise.

Hi, @uriah1977. Apologies for a bit of delay in getting back to you. I was off work and then the weekend.

I have tried your suggestion and whilst the result works, as you and other contributors have commented/ suspected, it doesnt apply by dates and as such the outstanding result is not accurately captured.

We hoped the result will be is to show the last invoice(s) as outstanding and the earlier ones as paid. In this scenario, it doesn’t strictly follow that logic.

I am desperately hoping that someone from Enterprise DNA would also jump in to help us out.

Thank you.