Combining multiple 'What Ifs' to get 3rd value

Question

&nbsp;I need to implement What-if analysis.&nbsp;I created 3 Parameters: Exp_IN, Exp_OUT, Exp_Date.&nbsp;I have data like below:Created_Date&nbsp; &nbsp;Issue_IN&nbsp; &nbsp;Issue_OUT&nbsp; &nbsp;Issue_Balance&nbsp; &nbsp; Cumm_Open&nbsp; &nbsp; &nbsp; &nbsp; Expected_Date11/01/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11/03/201711/02/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11/04/201711/03/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11/05/201711/04/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11/06/2017&nbsp;Now based on any 2 Selection, I need to evaluate the expected 3rd result for Future .&nbsp;Case- 1 : If Select Exp_IN=5 and Exp_Out=3, then what will be the Expected Date, when cumm_Balance will be = 0Case- 2 : If Select Exp_IN=5 and Exp_Date=11/07/2017, then what should be the Exp_OUT, when cumm_Balance will be =&nbsp;0Case- 3 : If Select Exp_Out=4 and Exp_Date=11/07/2017, then what should be&nbsp;Exp_IN, when cumm_Balance will be =&nbsp;0&nbsp;Thanks in advance for your help&nbsp;on this problem.

shishir999 · Answer

&nbsp;I need to implement What-if analysis.&nbsp;I created 3 Parameters: Exp_IN, Exp_OUT, Exp_Date.&nbsp;I have data like below:Created_Date&nbsp; &nbsp;Issue_IN&nbsp; &nbsp;Issue_OUT&nbsp; &nbsp;Issue_Balance&nbsp; &nbsp; Cumm_Open&nbsp; &nbsp; &nbsp; &nbsp; Expected_Date11/01/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11/03/201711/02/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11/04/201711/03/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11/05/201711/04/2017&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 11/06/2017&nbsp;Now based on any 2 Selection, I need to evaluate the expected 3rd result.&nbsp;Case- 1 : If Select Exp_IN=5 and Exp_Out=3, then what will be the Expected Date, when cumm_Balance will be = 0Case- 2 : If Select Exp_IN=5 and Exp_Date=11/07/2017, then what should be the Exp_OUT, when cumm_Balance will be =&nbsp;0Case- 3 : If Select Exp_Out=4 and Exp_Date=11/07/2017, then what should be&nbsp;Exp_IN, when cumm_Balance will be =&nbsp;0&nbsp;Thanks in advance for your help&nbsp;on this problem.

marcelbeug · Answer

Please explain the logic (I guess that will be about a page of text and examples (data and expected output)).&nbsp;Are you looking for a DAX or a Powerr Query solution?

shishir999 · Answer

I want to use DAX way (learner in DAX though). Here is the scenario: I have 3 Parameters IN (new), OUT (resolve), and No_of_Days (by when). Also, Total_Open (EoD) (Card Display) The underlying formula: No_of_days = Total_Open / (Out-IN) (eg. IN-5, OUT-8 then No_Of_Days= 10 / (8-5) =3.3 ~~4 ; or IN-5, No_Of_Days=3 then OUT= (10/3 )+5) I have a total no of issues (10) open in my bucket (Today). I want to achieve 3 scenario dynamically: 1. By when my open issues become ZERO (0), by changing values of 2 Selected parameters (IN, OUT) by user. 2. What value of OUT would be, to make issues ZERO (0), by changing values of 2 Selected parameters (IN, No_Of_Days) by user. 3. What value of IN would be, to make issues ZERO (0), by changing values of 2 Selected parameters (OUT, No_Of_Days) by user. 4. Also, I want to provide values for 2 parameters ONLY from the 3 parameters and want to get value of 3rd one ONLY. Can we able to restrict user able to Select only 2 parameters...? Please help.

shishir999 · Answer

Hi Marcel,I am able to solve the criteria. But, now get stuck here.Please see the sample data:DataSet-1:Created_Date IN OUT Balance Expected_Date Cumm_Bal11/20/2017 8 4 4 11/23/2017 411/21/2017 7 3 4 11/24/2017 811/22/2017 6 2 4 11/25/2017 12Need to Predict expected days, when value become ZERO (0).Cumm_Bal: 12Expected_Date IN OUT Balance Exp_Zero11/23/2017 3 5 -2 10 Cumm_Bal + Balance11/24/2017 3 5 -2 8 Exp_Zero (1) + Balance11/25/2017 3 5 -2 611/26/2017 3 5 -2 411/27/2017 3 5 -2 211/28/2017 3 5 -2 0No_Of_Days: 6&nbsp;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.&nbsp;Also, can we able to restrict user, will be able to Select only 2 parameters...?&nbsp;Please help.

Forum Discussion

Combining multiple 'What Ifs' to get 3rd value

4 Replies

Related content

Power Query Combine Multiple Files From Folder

Tables combine

Combine values from multiple rows

Combine multiple measures into one

Append/ Combine Multiple Json Files