Forum Discussion

sjrrkb123's avatar
sjrrkb123
Helper III
5 years ago
Solved

Cumulative DAX function showing duplication on rows

I am working with a stream of payments for a given invoice. What I am trying to do is show a running balance of invoices.

Here is what my data looks like:

Invoice Date Payment Type Payment Aging Group 
1A1 

1/1/2020

 

Invoice 1000 Current 
1A1 1/4/2020 Cash Received -500 1-30 Days Past Due 
1A1 1/5/2020 Cash Received -500 1-30 Days Past Due 
1B1 2/4/2020 Invoice 2000 Current 
1B1 2/5/2020 Cash Received -1500 1-30 Days Past Due 
1B1 2/20/2020 Cash Received -250 1-30 Days Past Due 
1B1 2/21/2020 Cash Received -200 1-30 Days Past Due 
1B1 3/1/2020 Cash Received -50 1-30 Days Past Due 


Here is the calculation I am using for the running balance:
Payment Stream = SUM('Current State'[Payment])
Running Balance = 
   var max_date = MAX('Dates - Dim Dates'[Calendar Date]
RETURN
Calculate([Payment Stream],

     ALLEXCEPT('Dates - Dim Dates', 'Dates - Dim Dates'[Calendar Date]),
     'Dates - Dim Dates'[Calendar Date] <= max_date)

Current Problems:
When I try to put the aged balance groups in the stacked column chart, it splits the payments out so that I get an odd-looking graph. Basically, the application of Aged Balance Groups to the legend splits the payments out in a bad way. The left is no aged groups and the right has them. On the bottom, the left matrix shows the running balance calculations and the right is without.

 


What I do not know how to do is 
1. Not have the running balance calculation affected by Payment Type and Aging Group.
2. Finally, how to get the dates so that they do not continue past the last payment date for a given invoice. Just realized this is occurring due to (1.) 🙂
 

EDIT: To clarify, the goal is to show the invoiced amount on a stacked chart on the date it occurs. That bar remains on the chart for every day until there is a payment. If the payment is in full then the original invoiced amount goes to 0 and the bar disappears. If there is only partial cash payment, then the bar goes down by the amount of cash received. So, for 1A1, the invoiced amount of $1000 would be on the chart from 1/1/2020 to 1/3/2020. On 1/4/2020 there is a cash payment so the bar goes down by the amount of the cash payment. Ideally, this then would be sliced with the aging groups to show an Accounts Receivable balance over time. 

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi sjrrkb123,

    For your scenario, I'd like or suggest you create an unconnected calendar table and use the following measure expression to replace the display value based on the new calendar date and current payment type.

    New calendar:

    Unconnected = VALUES('Dates - Dim Dates')

    Measure expressions:

    Rolling = 
VAR max_date =
    MAX ( 'Unconnected'[Calendar Date] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Current State'[Payment Type] ),
        "Invoice",
            CALCULATE (
                SUM ( 'Current State'[Payment] ),
                FILTER ( ALLSELECTED ( 'Current State' ), [Date] <= max_date )
            ),
        CALCULATE (
            ABS ( SUM ( 'Current State'[Payment] ) ),
            FILTER ( ALLSELECTED ( 'Current State' ), [Date] <= max_date ),
            VALUES ( 'Current State'[Payment Type] )
        )
    )

    Regards,

    Xiaoxin Sheng

2 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hi sjrrkb123,

      For your scenario, I'd like or suggest you create an unconnected calendar table and use the following measure expression to replace the display value based on the new calendar date and current payment type.

      New calendar:

      Unconnected = VALUES('Dates - Dim Dates')

      Measure expressions:

      Rolling = 
VAR max_date =
    MAX ( 'Unconnected'[Calendar Date] )
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Current State'[Payment Type] ),
        "Invoice",
            CALCULATE (
                SUM ( 'Current State'[Payment] ),
                FILTER ( ALLSELECTED ( 'Current State' ), [Date] <= max_date )
            ),
        CALCULATE (
            ABS ( SUM ( 'Current State'[Payment] ) ),
            FILTER ( ALLSELECTED ( 'Current State' ), [Date] <= max_date ),
            VALUES ( 'Current State'[Payment Type] )
        )
    )

      Regards,

      Xiaoxin Sheng