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

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