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 🙂
Overdue = CALCULATE(SUM(BS_GK_[CREDIT])-SUM(BS_GK[DEBIT]),FILTER(BS_GK_,BS_GK[DUE_DATE]<TODAY()))
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 |
