What-If on Date Column

[immkhan]

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

[Greg_Deckler · Accepted solution]

@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])

[immkhan]

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