Forum Discussion
What-If on Date Column
Hi,
I have to apply the What-if scenario on Date but unable to do so. My data is like: (All are data and calculated columns; no Measures)
Product Lot Total_Shelf_Life Rem_Shelf_Life Rem_Shelf_Life% Rem_Shelf_Life_Bracket Quantity
Item A 12 24 (months) 13 54 % 50% - 75% 30
Item A 21 36 (months) 10 28 % 25% - 50% 20
Item A 123 24 (months) 5 21 % Below 25% 10
Item A 112 24 (months) 15 63 % 50% - 75% 40
Item A 142 24 (months) 18 75 % 50% - 75% 50
Now I have created a line chart graph in which "Rem_Shelf_Life_Bracket" is on Axis and Quantity is on Values. The graph plotted fine. Example is like :
Consider values as following for Brackets:
Below 25% = 10
25% - 50% = 20
50% - 75% = 120
Now I want to create a What-if Scenario for Rem Shelf Life. User wants to see where the values will be if the Remaining Shelf Life decreases by a Month or Two.
For that, I created Adjusted Month Parameter in which user will mention the values from -1 to -12. Now the problem is how can i calculate the measure and change the values in Rem_Shelf_Life_Bracket.
Example: If user select Adjusted month as "-2" then another line should appear with values
Below 25% = 30
25% - 50% = 70
50% - 75% = 50
Please help me out how can I do it.
Regards
immkhan - It is hard to explain the solution so I have opted to just present a PBIX file (attached below sig). The measure used is below:
Measure 2 = VAR __Adjustment = SELECTEDVALUE('Adjusted'[Adjusted]) VAR __Bracket = SELECTEDVALUE('Brackets'[Bracket]) VAR __High = SWITCH(__Bracket, "Below 25%",.25, "25% - 50%",.50, "50% - 75%",.75, 1 ) VAR __Low = SWITCH(__Bracket, "Below 25%",0, "25% - 50%",.2500001, "50% - 75%",.5000001, .75000001 ) VAR __Table = ADDCOLUMNS( 'Table', "__Rem_Shelf_Life%",([Rem_Shelf_Life] + __Adjustment) / [Total_Shelf_Life] ) RETURN SUMX(FILTER(__Table,[__Rem_Shelf_Life%] >= __Low && [__Rem_Shelf_Life%]<=__High),[Quantity])
2 Replies
- Greg_Deckler
Community Champion
immkhan - It is hard to explain the solution so I have opted to just present a PBIX file (attached below sig). The measure used is below:
Measure 2 = VAR __Adjustment = SELECTEDVALUE('Adjusted'[Adjusted]) VAR __Bracket = SELECTEDVALUE('Brackets'[Bracket]) VAR __High = SWITCH(__Bracket, "Below 25%",.25, "25% - 50%",.50, "50% - 75%",.75, 1 ) VAR __Low = SWITCH(__Bracket, "Below 25%",0, "25% - 50%",.2500001, "50% - 75%",.5000001, .75000001 ) VAR __Table = ADDCOLUMNS( 'Table', "__Rem_Shelf_Life%",([Rem_Shelf_Life] + __Adjustment) / [Total_Shelf_Life] ) RETURN SUMX(FILTER(__Table,[__Rem_Shelf_Life%] >= __Low && [__Rem_Shelf_Life%]<=__High),[Quantity])Thanks for the reply and it is working. The only thing which is remaining is the second line. The graph is changing on the same line but I want to have default line at 0 days and second line on user selection.
Regards
Imran
