Cumulative Total Balance per Line Under Certain Conditions

[Ajungx]

InvTable

Inv. No	Date	Cst Name	Amount	Paid	Balance	CumBalance
IN-001	01/08/19	C001	1000	800	200	200
IN-002	15/09/19	C002	800	0	800	800
IN-003	20/09/19	C001	1200	400	800	1000
IN-004	09/10/19	C002	2200	1000	1200	2000
IN-005	28/11/19	C003	3000	2000	1000	1000
			8200	4200	4000

What is the formula to make the measure "CumBalance"?

Balance can only be added if the customer code is the same

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[v-zhenbw-msft · Accepted solution]

Hi @Ajungx ,

We can create a measure to meet your requirement.

CumBalance = 
CALCULATE (
    SUM ( 'Table'[Balance] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Cst Name] = MAX ( 'Table'[Cst Name] )
            && 'Table'[Date] <= MAX ( 'Table'[Date] )
    )
)

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

BTW, pbix as attached.

[amitchandak]

@Ajungx , Try with a date table

Cumm = CALCULATE(SUM(Table[Balance]),filter(date,date[date] <=maxx(date,date[date])))
Cumm = CALCULATE(SUM(Table[Sales Amount]),filter(date,date[date] <=max(Table[Date])))

or

Cumm = CALCULATE(SUM(Table[Balance]),filter(allselected(date),date[date] <=maxx(date,date[date])))

or

Cumm = CALCULATE(SUM(Table[Sales Amount]),filter(allselected(Table),Table[date] <=max(Table[Date])))