cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Cumulative Total Between Two Columns Where The First Set to Zero

Good Day, I have a situation that is illustrated by the table below:

 Date (Calendar Table) Customer Name Opening Balance Invoice Tot Subs Closing Balance July 1, 2019 A 0 100 20 80 July 2, 2019 A (closing balance goes here) 20 5 95

The idea is for the closing balance to become the opening balance at the start of each new day and continue on cumulatively. I have trouble ensuring that if this is the first day the customer is participating, the customer's opening balance is set to 0 and the closing balance keeps doing its thing onward.

Any suggestions?

1 ACCEPTED SOLUTION
Super User

@DA12345 , Try measure like

Opening balanace = calculate(sum(Table[Invoice]) - sum(Table[TotSubs]) , filter(allselected('Date'), Date[Date] < max(Date[Date])))

Closing Balance = calculate(sum(Table[Invoice]) - sum(Table[TotSubs]) , filter(allselected('Date'), Date[Date] <= max(Date[Date])))

if closing balance is already a column  with number

Opening balanace

= CALCULATE(SUM(Table[closing balance ]),previousday('Date'[Date]))

3 REPLIES 3
Super User

@DA12345 , Try measure like

Opening balanace = calculate(sum(Table[Invoice]) - sum(Table[TotSubs]) , filter(allselected('Date'), Date[Date] < max(Date[Date])))

Closing Balance = calculate(sum(Table[Invoice]) - sum(Table[TotSubs]) , filter(allselected('Date'), Date[Date] <= max(Date[Date])))

if closing balance is already a column  with number

Opening balanace

= CALCULATE(SUM(Table[closing balance ]),previousday('Date'[Date]))

Frequent Visitor

Thank you. This works to a limited degree however, I have a date slider on the page that influences that table, if the earliest date range is not selected in that range, It doesn't present a correct closing balance based on what come before.

Frequent Visitor

In my case, this I replaced ALLSELECTED() with ALL()

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors