Forum Discussion
Dynamic Accounts Receivable Aging
I have been banging my head against the wall trying to come up with an accounts receivable aging report that is dynamic. In my customer orders table, I have invoice due date. I also have invoice amount, invoice date, general ledger date, etc. I need to be able to pick any point in time and come up with an aging report. So, for example, if a customer owed $10,000 that was due due on October 1, 2017 and today is Novemer 15th, then this invoice is 45 days past due. Now roll the date back to October 15th, or forward to December 15th, the days past due are 15 and 60 respectively. I was trying to follow a previous post ( https://community.powerbi.com/t5/Desktop/Accounts-Receivable-Aging-Report/td-p/106367 ), but that doesn't respond to any kind of date slicer/picker. In the aforementioned example, the person replying had a separate calendar based on the minimum due date of the table and today. Then they used INT(MAX('Date'[Date])-MAX('Invoices'[InvoiceDueDate]). I get a static value. I have tried joining it to my primary date table on both the general ledger date and the invoice due date, but no luck. Once I can get the days past due to move with the date, then I can calculate my past due amounts. Thanks, in advance!!
Hi tlenzmeier,
If you want to keep the previous measure [PastDue], you can add another one:
Buckets = SWITCH ( TRUE (), ISBLANK ( [PastDue] ), "Current", [PastDue] >= 1 && [PastDue] <= 30, "Bucket1", [PastDue] >= 31 && [PastDue] <= 60, "Bucket2", [PastDue] >= 61 && [PastDue] <= 90, "Bucket3", [PastDue] >= 91, "Bucket4",
"Error" )If you only want one measure, you can use this one:
Buckets In One = VAR SelectedDate = IF ( HASONEVALUE ( 'Indicator'[Date] ), VALUES ( 'Indicator'[Date] ), BLANK () ) VAR PastDue = IF ( MIN ( 'Invoice'[ InvoiceDueDate] ) >= SelectedDate || ISBLANK ( SelectedDate ), BLANK (), DATEDIFF ( MIN ( 'Invoice'[ InvoiceDueDate] ), SelectedDate, DAY ) ) RETURN SWITCH ( TRUE (), ISBLANK ( [PastDue] ), "Current", [PastDue] >= 1 && [PastDue] <= 30, "Bucket1", [PastDue] >= 31 && [PastDue] <= 60, "Bucket2", [PastDue] >= 61 && [PastDue] <= 90, "Bucket3", [PastDue] >= 91, "Bucket4",
"Error" )Best Regards,
Dale
You are welcome.
If you want to add the buckets as column headers, I'm afraid it's too hard. The visual could be Matrix. Let's make some explanations.
1. There aren't any buckets in the source tables. They are dynamic.
2. The dynamic values can't be added in the column. Maybe we can create a new table of all the buckets. The problem is we can't create a relationship with the tables we have now.
Maybe there is a workaround. I would suggest you create a new thread in this forum to focus on this need.
Thank you for accepting my answer.
Best Regards,
Dale
8 Replies
Hi tlenzmeier,
Could you please share a sample? The formula you quoted needs a context to work. If you put it in a Card visual, you will get a static value. Usually a date table is needed in your scenario.
Best Regards!
Dale
I have two tables. The first table is my date dimension. The second table is my invoice table.
So it looks somehting like:
CustomerID, PrimaryKey, InvoiceAmount, InvoiceDueDate, GeneralLedgerDate.
There's a one-to-many join from the date dimension to the invoice table on the general ledger date. My objective is to determine how many days past due an invoice is as at any particular point in time. For example, if I have an invoice issued on October 1, 2017 and nothing has been paid, then on November 1, 2017 it would be 30 days past due. Similarly, on December 1, 2017, it would be 60 days past due. In SQL, it's pretty straightforward to do a DATEDIFF, but in this instance, I need to accomplish the same thing, but I one date comes from my date dimension and the other comes from the invoice table. I have tried numerous things, all of which end up with an error along the longs that one date can't be greater than the other.
Hi tlenzmeier,
The date column you need is an indicator rather than a filter. I would suggest adding another table to act as the indicator. You can check it out in this file.
PastDue = VAR SelectedDate = IF ( HASONEVALUE ( 'Indicator'[Date] ), VALUES ( 'Indicator'[Date] ), BLANK () ) RETURN IF ( MIN ( 'Invoice'[ InvoiceDueDate] ) >= SelectedDate || ISBLANK ( SelectedDate ), BLANK (), DATEDIFF ( MIN ( 'Invoice'[ InvoiceDueDate] ), SelectedDate, DAY ) )Best Regards,
Dale
