Forum Discussion
Accounts Payable overdue
Hello,
I am trying to solve one prblem with overdue and ageing. I can not solve that even in excel 🙂
Problem is that one vendor can have multiple invoices and multiple payments. There is no closing on Invoice row base but in a separate row which belongs to TYPE Payments. I need to calculate Total due for every vendor. And if it is possible to calculate ageing or overdue on basis 0-15, 16-30, >30. I tried hunderd times in excel to get some clue how it can be done, and to use that logic in Power Bi but without much success.
I tried to create some measures, but no 🙂
Many Thanks for any kind of help.
Here is table with one vendor and its invoice and payments and due dates.
| TYPE | IDATE | DUE_DATE | ACCOUNT | VENDOR_CODE | VENDOR_NAME | DOCUMENT | DEBIT | CREDIT | BALANCE |
| Invoice | 01.01.21 | 05.03.21 | 4350 | P-90059 | PLATINUM | A1 | 0,00 | 10.000,00 | -10.000,00 |
| Invoice | 01.01.21 | 05.04.21 | 4350 | P-90059 | PLATINUM | A2 | 0,00 | 12.000,00 | -12.000,00 |
| Invoice | 01.01.21 | 10.04.21 | 4350 | P-90059 | PLATINUM | A3 | 0,00 | 3.500,00 | -3.500,00 |
| Invoice | 01.01.21 | 15.05.21 | 4350 | P-90059 | PLATINUM | A4 | 0,00 | 5.000,00 | -5.000,00 |
| Invoice | 01.01.21 | 25.05.21 | 4350 | P-90059 | PLATINUM | A5 | 0,00 | 10.500,00 | -10.500,00 |
| Invoice | 01.01.21 | 25.06.21 | 4350 | P-90059 | PLATINUM | A6 | 0,00 | 2.500,00 | -2.500,00 |
| Invoice | 01.01.21 | 27.06.21 | 4350 | P-90059 | PLATINUM | A7 | 0,00 | 4.000,00 | -4.000,00 |
| Payment | 05.03.21 | 4350 | P-90059 | PLATINUM | Payment 1 | 20.000,00 | 0,00 | 20.000,00 | |
| Payment | 20.04.21 | 4350 | P-90059 | PLATINUM | Payment 2 | 10.000,00 | 0,00 | 10.000,00 | |
| Payment | 31.05.21 | 4350 | P-90059 | PLATINUM | Payment 3 | 5.000,00 | 0,00 | 5.000,00 |
2 Replies
- amitchandak
Super User
sassaboss , to have a bucket on measure, you need to do dynamic segmenetation.
That need independent table and new measures to use that
refer to my blog and video
Dynamic Segmentation Bucketing Binning
https://community.powerbi.com/t5/Quick-Measures-Gallery/Dynamic-Segmentation-Bucketing-Binning/m-p/1387187#M626
Dynamic Segmentation, Bucketing or Binning: https://youtu.be/CuczXPj0N-kThanks a lot. I will watch video and try to figure out some kind of solution.
In mean time I am breaking my head in Excel. I used those formulas and maybe I am going toward some primitive solution 🙂
Simply said I have to figure on "paper"
