Hi all,
Need some help with aging debtors invoice in powerpivot. I'm a total newbie to Powerpivot.
I followed the blog post at these two websites:
Aged Debtors–Dynamic Banding in DAX « PowerPivotPro
Excel extras: aged debtors in PowerPivot
I can get the first formula (Debtor Value) to work as demonstrated, however, I cannot get the second formula to apply the banding. It calculates the correct amount by payor, but doesn't break it down into the aging buckets.
Obviously my tables are somewhat different to the example. Basically I have a table with all of the invoices and the invoice date (table: Amount Details), I have that linked to a "date key" table (table: Periods) by the invoice date. Plus I have the "bands" table (table: Aging Bands).
Here are the formulae:
Debtor Value:=Calculate(SUM('Amount Details'[Net Amount]),filter(ALL(Periods),COUNTROWS(FILTER(Periods,EARLIER(Periods[End Of Month])<=MAX(Periods[End Of Month])))>0))
Aged Debtor:=if(COUNTROWS('Aging Bands')=1,CALCULATE([Debtor Value],FILTER(Periods,MAXX('Transaction Details',MAX(Periods[End Of Month])-EARLIER(Periods[Date]))>=MAX('Aging Bands'[Days From])&&MAXX('Transaction Details',MAX(Periods[End Of Month])-EARLIER(Periods[Date]))<=MAX('Aging Bands'[Days To]))),[Debtor Value])
Any help would be awesome. Thank you!
Matt
Need some help with aging debtors invoice in powerpivot. I'm a total newbie to Powerpivot.
I followed the blog post at these two websites:
Aged Debtors–Dynamic Banding in DAX « PowerPivotPro
Excel extras: aged debtors in PowerPivot
I can get the first formula (Debtor Value) to work as demonstrated, however, I cannot get the second formula to apply the banding. It calculates the correct amount by payor, but doesn't break it down into the aging buckets.
Obviously my tables are somewhat different to the example. Basically I have a table with all of the invoices and the invoice date (table: Amount Details), I have that linked to a "date key" table (table: Periods) by the invoice date. Plus I have the "bands" table (table: Aging Bands).
Here are the formulae:
Debtor Value:=Calculate(SUM('Amount Details'[Net Amount]),filter(ALL(Periods),COUNTROWS(FILTER(Periods,EARLIER(Periods[End Of Month])<=MAX(Periods[End Of Month])))>0))
Aged Debtor:=if(COUNTROWS('Aging Bands')=1,CALCULATE([Debtor Value],FILTER(Periods,MAXX('Transaction Details',MAX(Periods[End Of Month])-EARLIER(Periods[Date]))>=MAX('Aging Bands'[Days From])&&MAXX('Transaction Details',MAX(Periods[End Of Month])-EARLIER(Periods[Date]))<=MAX('Aging Bands'[Days To]))),[Debtor Value])
Any help would be awesome. Thank you!
Matt
Last edited:
