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

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

Hi robarivas,

Please use the following DAX and check if you can get expected result.

=
VAR X =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( TransactionsTable[Posting_Date] ), ALL ( TransactionsTable ) )
            && DATEDIFF ( MAX ( TransactionsTable[CustomerCheckoutDate] ), 'Date'[Date], DAY )
                < 31,
        CALCULATE (
            SUM ( TransactionsTable[Transaction_Amount] ),
            FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= X )
        )
    )

Best Regards,
Angelia

  • robarivas's avatar
    robarivas
    Post Patron
    8 years ago

    Hello v-huizhn-msft. Thank you for the reply.  Unfortunately it kicked back the following error:

     

    "A single value for column 'Date' in table 'Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

     

    Below is the portion of the formula that appears to have caused the error:

     

    =
VAR X =
    MAX ( 'Date'[Date] )
RETURN
    IF (
        MIN ( 'Date'[Date] )
            <= CALCULATE ( MAX ( TransactionsTable[Posting_Date] ), ALL ( TransactionsTable ) )
            && DATEDIFF ( MAX ( TransactionsTable[CustomerCheckoutDate] ), 'Date'[Date], DAY )
                < 31,
        CALCULATE (
            SUM ( TransactionsTable[Transaction_Amount] ),
            FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= X )
        )
    )