cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GeraldZ Regular Visitor
Regular Visitor

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

 

Problem is that if i try to extract it into another table the iterations break and don't return appropriate values.  Have tried the SUMMARIZE and ADD columns functions to no avail.  Anyone run into this and been able to extract iterative calculations as they appear in the table visualization?


Would appreciate the help!

4 REPLIES 4
GeraldZ Regular Visitor
Regular Visitor

Re: Extracting iterative calculations in visualization to new table

Really need everyone's help, please.  

 

Here is the structure of the iterative calculation:

 

CUMAdjMiscAdjustments = Calculate(
[SUMAdjMiscAdjustments],
FILTER(
ALL(PrincipalEdit[CurrentDate]),
PrincipalEdit[CurrentDate]<= MAX(PrincipalEdit[CurrentDate])))
 
I am taking cumulative totals using the date as the reference/anchor point.  The above is just a sample of a few of the factors that I am calculating.
 
The factors add up to a total for the closing principal balance, see below for formula:
 
ClosingPrincipal = [StartingBalanceMOB0]+[CUMDisbursementsNEW]+[CUMDisbursementsREW]+[CUMOrigFeeTXIL]+[CUMPrinCOwTXIL]+[CUMPrepaidPrincipal]+[CUMSchedPrincipalPayment]+[CUMPrincipalRewrites]+[CUMPrincipalRefinance]+[CUMCredits]+[CUMOverpaidPrincipal]+[CUMAdjMiscAdjustments]
 
The iteration is that the Opening principal for the next month is the closing principal for the PRIOR month.  Formula for the Opening principal is below:
 
OpeningPrincipal = Calculate(
[ClosingPrincipal],
FILTER(
ALL(PrincipalEdit[CurrentDate]),
PrincipalEdit[CurrentDate]<=MIN(PrincipalEdit[CurrentDate])-1))
 
The above tells PowerBI to pull the closing principal date for the month prior.
 
Now this all works great when i put it into a TABLE visualization, but i can't extract this data (I have more than 30K rows) and I can't pull this into a table because the iteration for the measures breaks when i pull it into another table using the SUMMARIZE or ADDCOLUMNS formulas.
 
Has anyone been able to succeed in doing something similar?  Can you please share how you were able to take the calculated data in the visualization table and either extract it or place it into a new table?
 
Appreciate the help.
Community Support Team
Community Support Team

Re: Extracting iterative calculations in visualization to new table

Hi @GeraldZ ,

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

 

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
GeraldZ Regular Visitor
Regular Visitor

Re: Extracting iterative calculations in visualization to new table

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.
Highlighted
GeraldZ Regular Visitor
Regular Visitor

Re: Extracting iterative calculations in visualization to new table

Hi there, can anyone help please?

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 269 members 3,132 guests
Please welcome our newest community members: