Recursive Calculation for What-If scenario-SG

---

shishir999 wrote:
I get stuck here. Please see the sample data: DataSet-1: Created_Date IN OUT Balance Expected_Date Cumm_Bal 11/21/2017 8 4 4 11/23/2017 4 11/22/2017 7 3 4 11/24/2017 8 11/23/2017 6 2 4 11/25/2017 12 Need to Predict expected days, when value become ZERO (0). Cumm_Bal: 12 Expected_Date IN OUT Balance Exp_Zero 11/24/2017 3 5 -2 10 Cumm_Bal + Balance 11/25/2017 3 5 -2 8 Exp_Zero (1) + Balance 11/26/2017 3 5 -2 6 11/27/2017 3 5 -2 4 11/28/2017 3 5 -2 2 11/29/2017 3 5 -2 0 So, Total No_Of_Days= 6 Here, I'm able to calculate Balance under 2nd dataset. But, unable to get Exp_ZERO value, on a recursive basis on last Exp_Zero value. Also, can we able to restrict user, will be able to Select only 2 parameters...? Thanks in Advance for help.

---

shishir999

Per my understanding, you can create What_If_In and What_If_Out parameteres and measures as below. See more in the attached pbix file.

Cumm_Bal = SUM(Table1[IN])-SUM(Table1[OUT])

Total No_Of_Days = 
VAR diff = What_If_In[What_If_In Value]-What_If_Out[What_If_Out Value]
RETURN 
IF(diff>0,999, DIVIDE([Cumm_Bal],diff*-1)
)

Thanks Eric for the solution, but it didnot work.

I created 2 parameters (IN, OUT) and the measure, but the caluculation is not working as expected.

Cumm_Bal = Sum(Par[IN]) - Sum(Par[OUT])

Here is the data & code: 

-------------------------------------------
Date                myIN    myOUT   myBal   myPending

11/27/17         3             8           -5           12

11/28/17         3             8            -5            7      This must be the output

11/29/17         3             8             -5            2      this is based on [last(myPending)-currBal]

11/30/17         3        8        -5            0

--------------------------------------------- 

So No_days=3 days

-------- 
myPending =
var pen = Table2[Cumm_Bal]
var den =pen+Table2[myBal]
Return
IF(Table2[Date]=TODAY(), pen,
  CALCULATE(den,
     FILTER(Table2,
      Table2[Date]<=MAX(Table2[Date])
)))

--------

Please advise, what is wrong here. I need these values (myIN, myOUT, myPending), as need to draw these value in chart based on existing Table1.