Forum Discussion

Anonymous

Not applicable

7 years ago

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...

Icon for Resident Rockstar rank

Resident Rockstar

7 years ago

Hi 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

Anonymous
Not applicable
7 years ago
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.
Anonymous
Not applicable
7 years ago
Hi there, can anyone help please?