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

Combining multiple 'What Ifs' to get 3rd value

 

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.

4 REPLIES 4
shishir999 Regular Visitor
Regular Visitor

Use multiple 'What Ifs' to get 3rd Value

 

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.

MarcelBeug Super Contributor
Super Contributor

Re: Combining multiple 'What Ifs' to get 3rd value

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?

Specializing in Power Query Formula Language (M)
Highlighted
shishir999 Regular Visitor
Regular Visitor

Re: Combining multiple 'What Ifs' to get 3rd value

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 Regular Visitor
Regular Visitor

Re: Combining multiple 'What Ifs' to get 3rd value

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.

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 68 members 986 guests
Please welcome our newest community members: