Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only

Measure that reacts to values from another measure that depends on what-if parameter

  • 1.  Measure that reacts to values from another measure that depends on what-if parameter

    Posted Jul 04, 2022 09:23 PM

    I have the following line chart:

    enter image description hereWhere:

    (Dotted Yellow line) Receipts Issued Forecast = 
    VAR Forecast =
    CALCULATE(
        SUM(Estimate[Receipts issued]),
        DATEADD(dimDate[Date], -1, YEAR)
    )*'Production Rate %'[Production Rate % Value]
    RETURN
    IF(
        YEAR(MAX(dimDate[Date]))>YEAR(TODAY()),
        Forecast,
        SUM(Estimate[Receipts issued])
    )
    
    (Solid Purple Line) Receipts Issued = Estimate[Receipts Issued]
    
    (Dotted Purple Line) Projected Estimated Demand = CALCULATE(SUM(Estimate[Demand Estimate (Excl 2020-2022)]), FILTER(Estimate, Estimate[Start of Month] >= TODAY()))
    
    (Dotted Orange Line) Estimated Demand = CALCULATE(SUM(Estimate[Demand Estimate (Excl 2020-2022)]), FILTER(Estimate, Estimate[Start of Month] <= TODAY()))


    The dotted yellow line projects a forecast of receipts that is likely to be issued based on the average number of receipts issued every month prior. The yellow line can also be manipulated by users with a what-if parameter and a slider to adjust the percentage multiplier to see what would happen if the issuance rate of receipts increases by a given percentage amount.

    At issue is that I need the dotted purple line, which is also a projection, to adjust to the modified values of the dotted yellow line when the user plays with it. The dotted purple line is therefore the adjusted values of the dotted orange line which represents the estimated demand for receipts which uses the following logic based on the following data set (Where G = the resulting column Receipts Issued Forecast):

    Row      Calculation              Expected Result
    1 D1*E1 339478
    2 (D2*E2)+(G1-F2) 52319
    3 (D3*E3)+(G2-F3) -105376
    4 (D4*E4)+(G3-F4) -163264
    5 (D5*E5)+(G4-F5) -221152
    6 (D6*E6)+(G5-F6) -55425
    7 (D7*E7)+(G6-F7) -246910


    I've tried to figure this out by doing this:

    Projected Estimated Demand = CALCULATE(SUM(Estimate[Demand Estimate (Excl 2020-2022)]), FILTER(Estimate, Estimate[Start of Month] >= TODAY())) - [Receipts Issued Forecast]


    but the result doesn't behave the way I anticipate:

    As you can see, the dotted purple line jumps back up to the original estimated demand once the yellow dotted line runs out of values rather than continue the projection.

    So to reiterate, the dotted purple line (projected estimated demand) is a continuation of the dotted orange line (estimated demand). At a Production rate % multiplier of 0, the dotted purple line essentially continues the upward trend of the dotted orange line. What I need is for the dotted purple line to react to the increased receipts issued forecast (dotted yellow line) such that when the forecast line increases, the purple line decreases accordingly.

    As such, the following logic applies to the dotted purple line:

    Row      Calculation             
    1 Starts at the start of today's month using the corresponding value of the dotted orange line minus the value of the dotted yellow line for this month.
    2 (D2*E2)+(G1-F2)-value of the dotted yellow line
    3 (D3*E3)+(G2-F3)-value of the dotted yellow line
    4 (D4*E4)+(G3-F4)-value of the dotted yellow line
    5 (D5*E5)+(G4-F5)-value of the dotted yellow line
    6 (D6*E6)+(G5-F6)-value of the dotted yellow line
    7 (D7*E7)+(G6-F7)-value of the dotted yellow line

    I think the crux of the problem is that the first value of the purple line doesn't use the value of the orange line. Any ideas on how to adjust the formula for the dotted purple line?
    PBIX can be downloaded here


    ------------------------------
    Tom Paulson
    ------------------------------