Forum Discussion
Cumulative Total Balance per Line Under Certain Conditions
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
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.
3 Replies
- amitchandak
Super User
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])))
v-zhenbw-msftCommunity Support
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.
- Ashish_Mathur
Hi,
Share the link from where i can download your PBI file.
