Forum Discussion

robarivas's avatar
robarivas
Post Patron
8 years ago
Solved

DAX help - Accounts Receivable Aging

Hello. The Cumulative Balance pattern works well for me for trending Total Accounts Receivable. But I'm having trouble adapting it to subsets of the total (e.g., by account age group). Here is the no...
Need Help
  • robarivas's avatar
    robarivas
    8 years ago

    Unfortunately that did not work. But luckily I stumbled on a formula that works. Here it is in case anyone is interested. I'd be happy to hear any suggestions as to improvements to this formula if any exist.

     

    Total AR 0-30 =
    VAR EndDate =
        MAX ( 'Calendar'[Date] )
    RETURN
        IF (
            MIN ( 'Calendar'[Date] )
                <= CALCULATE ( MAX ('TransactionsTable'[PostingDate]), ALL ('TransactionsTable') ),
            CALCULATE (
                SUM ( 'TransactionsTable'[TransactionAmount] ),
                FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= EndDate ),
                KEEPFILTERS (
                    IFERROR (
                        DATEDIFF ( CustomerTable[CustomerCheckoutDate], EndDate, DAY ),
                        ( DATEDIFF ( EndDate, CustomerTable[CustomerCheckoutDate], DAY ) ) * -1
                    )
                        < 31
                )
             )
         )

Stachu's avatar
Stachu
Community Champion
8 years ago

can you share anonymized sample rows for the tables used?
is 31 the only age group you cover, or are there others, if so, what are they?

robarivas's avatar
robarivas
Post Patron
8 years ago

Hello Stachu. Thanks for your reply. Below is some sample data. I have a calendar table that is connected to the table below on the Transaction Date.

 

Ultimately I would want age groups for every 30 days up to 180 and then 180+. But I figured if I could get 0-30 figured out then I could take it from there to do the other buckets.

 

Account #Item #Transaction DateTransaction AmountCustomerCheckoutDate
15325553/14/2018 $                     4,214.004/4/2018
11341343/21/2018 $                     1,354.004/4/2018
14134432/6/2018 $                     1,661.004/4/2018
14121414/5/2018 $                     1,838.004/4/2018
25858112/13/2018 $                     2,065.002/22/2018
21475472/24/2018 $                     4,238.002/22/2018
24547872/9/2018 $                     3,698.002/22/2018
  • Stachu's avatar
    Stachu
    Community Champion
    8 years ago

    hmm, can you try this?

    LessThan31 =
VAR DaysOverdue =
    ADDCOLUMNS (
        Transactions,
        "DaysOverdue", Transactions[CustomerCheckoutDate] - Transactions[Transaction Date]
    )
VAR LessThan31 =
    FILTER ( DaysOverdue, [DaysOverdue] < 31 )
RETURN
    CALCULATE ( SUM ( Transactions[Transaction Amount] ), LessThan31 )

    I wasn't clear whether you need to calculate the days on row level (current syntax) or grouped per Account/Account&Item, if grouping is possible then Summarize could replace whole Transactions table

    Also the problem becones very easy once you add calculated column for 

    Transactions[CustomerCheckoutDate] - Transactions[Transaction Date]

    the question is whether it makes sense from aggregation angle

    • robarivas's avatar
      robarivas
      Post Patron
      8 years ago

      Unfortunately that did not work. But luckily I stumbled on a formula that works. Here it is in case anyone is interested. I'd be happy to hear any suggestions as to improvements to this formula if any exist.

       

      Total AR 0-30 =
      VAR EndDate =
          MAX ( 'Calendar'[Date] )
      RETURN
          IF (
              MIN ( 'Calendar'[Date] )
                  <= CALCULATE ( MAX ('TransactionsTable'[PostingDate]), ALL ('TransactionsTable') ),
              CALCULATE (
                  SUM ( 'TransactionsTable'[TransactionAmount] ),
                  FILTER ( ALL ( 'Calendar'[Date] ), 'Calendar'[Date] <= EndDate ),
                  KEEPFILTERS (
                      IFERROR (
                          DATEDIFF ( CustomerTable[CustomerCheckoutDate], EndDate, DAY ),
                          ( DATEDIFF ( EndDate, CustomerTable[CustomerCheckoutDate], DAY ) ) * -1
                      )
                          < 31
                  )
               )
           )

      • v-huizhn-msft's avatar
        v-huizhn-msft
        Microsoft Employee
        8 years ago

        Hi robarivas,

        Congratulations, please mark your reply as answer, so more people will benefit from here.

        Thanks,
        Angelia