I’m trying to get aged banding of accounts receivables to work dynamically from date selected in a slicer.
I’ve been successful in getting the overall balance as a running total and can calculate the outstanding days at a point in time. I thought the hard part was done at this point and all I needed to do was band it!
I have watched Sam’s videos on aged trial balance and others on segmenting but my data is structured a little differently and I simply can’t get it to work at all. I’m good with the concept of bands etc and have made that work really well in other pbix files but this one is tough.
The issue is one with the data in the fact file which lists both invoices and payments and their dates, (could have multiple payments for one invoice where customer paying in instalments).
Attached pbix is a watered down anonymised version for a customer paying in instalments. The bottom matrix is correct and shows the outstandings OK, (albeit outstanding days only shows on the day of a transaction) but I’m expecting the top matrix to be showing the ageing of that balance at each month. In this example the balance should show in the 29 Days + column for most of its time. Instead I’m seeing the transaction for the month, not the running total aged.
I think I’ve explained the problem. Hope you can help. Pbix attachedDebtorsEDNA.pbix (395.5 KB)