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