Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
tlenzmeier
Helper II
Helper II

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

2 ACCEPTED SOLUTIONS

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" )

Dynamic Accounts Receivable Aging.png

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

8 REPLIES 8
v-jiascu-msft
Employee
Employee

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

Dynamic_Accounts_Receivable_Aging

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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!

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" )

Dynamic Accounts Receivable Aging.png

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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?

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.