Hi
I’m adapting and extending EDNA’s Accounts Receivables Report to fit with my organisation (learning from the best right?). I’ve added a page which looks at Receipts in a given period simply by looking at items with an allocation date and whether that fits inside our outside of the Date selection. While I’ve been able to pull through the Receipts fine, I’m struggling to put them into Aging Brackets and I think it’s because I need to use an inactive relationship through the Allocation Date rather than the Invoice Date.
This works for Aging my Receivables:
Receivables Aging = CALCULATE( [Invoice Value], FILTER( FILTER( SUMMARIZE( 'Invoices', [TX Ref], [Value], "OS", [Days Outstanding]), [Days Outstanding]>0), COUNTROWS( FILTER( 'Aged Debtor Brackets', [Days Outstanding] >= 'Aged Debtor Brackets'[Min] && [Days Outstanding] <= 'Aged Debtor Brackets'[Max] ) ) > 0 ) )
And I’ve been trying the same approach for Receipts… but it’s failing. I’ve tried adding in userelationship and getting no change to the result. Am I barking up the wrong tree here? Should I be trying a different approach or am I close?
Thanks for your help!
Receipts Aging =
CALCULATE([Receipt Value],
FILTER(
FILTER(
SUMMARIZE( ‘Invoices’, [TX Ref], [Value], “Pay Days”, [Days to be Paid]), [Days to be Paid]>0),
COUNTROWS(
FILTER(‘Aged Debtor Brackets’,
[Days to be Paid] >= ‘Aged Debtor Brackets’[Min] &&
[Days to be Paid] <= ‘Aged Debtor Brackets’[Max] ) ) >0))
Accounts Receivable Example.pbix (2.3 MB)