Forum Discussion

tlenzmeier's avatar
tlenzmeier
Helper II
8 years ago
Solved

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 amou...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    8 years ago

    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's avatar
    v-jiascu-msft
    8 years ago

    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

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

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

tlenzmeier's avatar
tlenzmeier
Helper II
8 years ago

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's avatar
    v-jiascu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      tlenzmeier
      Helper II
      8 years ago

      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's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        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