Forum Discussion
Extracting iterative calculations in visualization to new table
Hi there, I have built an iterative calculation table similar to the one described in this post: https://community.powerbi.com/t5/Desktop/Iterative-Stock-Cover-Calculation/td-p/574257 Proble...
v-piga-msft
Resident Rockstar
Resident RockstarHi Anonymous ,
If is convenient, could you share some data sample which could reproduce your scenario and your desired output so that we could help further on it.
Best Regards,
Cherry
Hi there,
Below is the sample data, quite simple really in terms of data (lol)
Column Names (| delimiter): Date|Disbursements|Payoff
Data:
1/1/19|100,000|0
2/1/19|0|-1000
3/1/19|0|-2000
The goal is to start with an opening balance of 0, end January with a closing balance of 100,000, then February the opening balance should be 100,000 and the closing balance should be 99,000 and so on.
By following OwenAuger 's sample i created the following sums/cumulative measures:
Sums:
Sumdisbursements = sum(Sample[Disbursements])
sumpayoff = sum(Sample[Payoff])
cumulatives:
CUMPayoff = Calculate(
[sumpayoff],
FILTER(
ALL(Sample[Date]),
Sample[Date]<= MAX(Sample[Date])))
CUMDisbursements = Calculate(
[Sumdisbursements],
FILTER(
ALL(Sample[Date]),
Sample[Date]<= MAX(Sample[Date])))
Closing balance calculation:
ClosingBalance = [CUMDisbursements]+[CUMPayoff]+[StartingBalance]
Opening balance calculation (its not working for me in the sample not sure why):
OpeningBalance = Calculate(
[ClosingBalance],
FILTER(
ALL('Sample'[Date]),
'Sample'[Date]<=MIN('Sample'[Date])-1))
Starting balance of 0
StartingBalance = 0
Need help in fixing the opening balance and in extracting the result of the table visualization into a new table...hope this makes sense.