Dynamic Accounts Receivable Aging

[tlenzmeier]

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!!

[v-jiascu-msft · Accepted solution]

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

[v-jiascu-msft]

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

[tlenzmeier]

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.

[v-jiascu-msft]

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

[tlenzmeier]

This looks promising. One last question, what would the DAX be to bucket the amounts by days past due? For example, less than one day past due is current, 1-30 is a bucket, 31-60 is a bucket, 61-90 is a bucket, and 90+ is a bucket.

Thanks!

[v-jiascu-msft · Accepted solution]

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

[tlenzmeier]

Dale,

First of all, thank you!!

Now one more question. I am needing to present this information in a visual and in a table. The visual could be a donut and the table would have the invoice number, invoice date, due date, days past due, and then the buckets across the header row as columns. Visually, then, when looking at the table, the user would see Buckets 1-4 and their corresponding dollar amounts.

Again, thank you! Very grateful.

[v-jiascu-msft · Accepted solution]

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

[Morten_DK]

I have succeded in having the buckets made by doing this:

Receivables Overdue Buckets =
SWITCH (
TRUE ();
[Receivables datediff] <= 0; "0 Days";
[Receivables datediff] > 0
&& [Receivables datediff] <= 14; "1-14 Days Overdue";
[Receivables datediff] > 14
&& [Receivables datediff] <= 30; "15-30 Days Overdue";
[Receivables datediff] > 30
&& [Receivables datediff] <= 90; "31-90 Days Overdue";
[Receivables datediff] > 90
&& [Receivables datediff] <= 360; "91-360 Days Overdue";
[Receivables datediff] > 360
&& [Receivables datediff] < 1000000; "Over 360 Days Overdue";
"Error"
)

That also works when put in rows, but I cant use it in a chart as X-axis, or as columns in a Matrix. Is there any way around that?