Cumulative DAX function showing duplication on rows

[sjrrkb123]

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. 

[sjrrkb123]

Here is the link to the .pbix file: dummy data 

[v-shex-msft · Accepted solution]

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