Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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?
Solved! Go to Solution.
@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 , 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]))
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.
In my case, this I replaced ALLSELECTED() with ALL()
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
116 | |
108 | |
107 | |
93 | |
70 |
User | Count |
---|---|
173 | |
135 | |
131 | |
96 | |
94 |