cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
robarivas
Helper V
Helper V

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 normal pattern:

 

IF (
    MIN ( 'Date'[DateKey] )
        <= CALCULATE ( MAX ( Transactions[DateKey] ), ALL ( Transactions ) ),
    CALCULATE (
        SUM ( Transactions[Quantity] ),
        FILTER (
            ALL ( 'Date'[Date] ),
            'Date'[Date] <= MAX ( 'Date'[Date] )
        )
    )
)
 
And here is my attempt, which isn't working:
IF (
      MIN ( 'Date'[Date] )
             <= CALCULATE ( MAX ( TransactionsTable[Posting_Date] ), ALL ( TransactionsTable ) ),
     CALCULATE (
             SUM ( TransactionsTable[Transaction_Amount] ),
             FILTER (
                   ALL ( 'Date'[Date] ),
                  'Date'[Date] <= MAX ( 'Date'[Date] )
                        && DATEDIFF ( MAX ( TransactionsTable[CustomerCheckoutDate] ), 'Date'[Date], DAY ) < 31
            )
      )
)

My Transactions table is over 70 million rows so the proposed solution must be very performant/efficient of course.
1 ACCEPTED SOLUTION

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

View solution in original post

7 REPLIES 7
Stachu
Community Champion
Community Champion

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?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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
Community Champion
Community Champion

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



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Proud to be a Super User!

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

View solution in original post

Hi @robarivas,

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

Thanks,
Angelia

v-huizhn-msft
Microsoft
Microsoft

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

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

 

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.