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,

 

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

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