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)
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.
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:
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.
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’ :
I might be completely wrong in my understanding but I did spent hell lot of time to reach till this point :)
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.
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
If Test O = 0, “Paid”
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
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.
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
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.
Thanks to the contributors of this post. Due to the length by which this post has been active, we are tagging it as Solved. For further questions related to this post, please make a new thread.
