Combining multiple 'What Ifs' to get 3rd value

[shishir999]

I need to implement What-if analysis. I created 3 Parameters: Exp_IN, Exp_OUT, Exp_Date.

I have data like below:

Created_Date   Issue_IN   Issue_OUT   Issue_Balance    Cumm_Open        Expected_Date

11/01/2017              5              3                   2                2                      11/03/2017

11/02/2017              4              3                   1                3                      11/04/2017

11/03/2017              5              4                   1                4                      11/05/2017

11/04/2017              5              2                   3                7                      11/06/2017

Now based on any 2 Selection, I need to evaluate the expected 3rd result for Future .

Case- 1 : If Select Exp_IN=5 and Exp_Out=3, then what will be the Expected Date, when cumm_Balance will be = 0

Case- 2 : If Select Exp_IN=5 and Exp_Date=11/07/2017, then what should be the Exp_OUT, when cumm_Balance will be = 0

Case- 3 : If Select Exp_Out=4 and Exp_Date=11/07/2017, then what should be Exp_IN, when cumm_Balance will be = 0

Thanks in advance for your help on this problem.

[MarcelBeug]

Please explain the logic (I guess that will be about a page of text and examples (data and expected output)).

Are you looking for a DAX or a Powerr Query solution?

[shishir999]

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_Bal
11/20/2017 8 4 4 11/23/2017 4
11/21/2017 7 3 4 11/24/2017 8
11/22/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/23/2017 3 5 -2 10 Cumm_Bal + Balance
11/24/2017 3 5 -2 8 Exp_Zero (1) + Balance
11/25/2017 3 5 -2 6
11/26/2017 3 5 -2 4
11/27/2017 3 5 -2 2
11/28/2017 3 5 -2 0

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

Please help.

[shishir999]

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]

I need to implement What-if analysis. I created 3 Parameters: Exp_IN, Exp_OUT, Exp_Date.

I have data like below:

Created_Date   Issue_IN   Issue_OUT   Issue_Balance    Cumm_Open        Expected_Date

11/01/2017              5              3                   2                2                      11/03/2017

11/02/2017              4              3                   1                3                      11/04/2017

11/03/2017              5              4                   1                4                      11/05/2017

11/04/2017              5              2                   3                7                      11/06/2017

Now based on any 2 Selection, I need to evaluate the expected 3rd result.

Case- 1 : If Select Exp_IN=5 and Exp_Out=3, then what will be the Expected Date, when cumm_Balance will be = 0

Case- 2 : If Select Exp_IN=5 and Exp_Date=11/07/2017, then what should be the Exp_OUT, when cumm_Balance will be = 0

Case- 3 : If Select Exp_Out=4 and Exp_Date=11/07/2017, then what should be Exp_IN, when cumm_Balance will be = 0

Thanks in advance for your help on this problem.