Forum Discussion

DA12345's avatar
DA12345
Frequent Visitor
5 years ago
Solved

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 ...
Need Help
Show and Tell
  • amitchandak's avatar
    amitchandak
    5 years ago

    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]))

amitchandak's avatar
amitchandak
Super User
5 years ago

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]))

  • DA12345's avatar
    DA12345
    Frequent Visitor
    5 years ago

    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.

    • DA12345's avatar
      DA12345
      Frequent Visitor
      5 years ago

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