Forecasting by daily scenario changes

[Mario]

Hy everyone, i need help for an forecasting scenario calculation. The forecasting calculation is based on the lattest video date harvest deep dive (https://youtu.be/mm-Lv6lv4a4) from @brain.

I have the following requirements:
I have an pre slicer calculation of sales

Total Sales = 
   SUMX(CustomerTransactionData,
    CustomerTransactionData[sales_qty]*CustomerTransactionData[sales_price])


Pre Slicer Total Sales (cum) = 
CALCULATE([Total Sales],
    DATESBETWEEN('Date'[Datum],[Min All Date],[Min Selected Date])
)

and i have an after slicer calculation of sales. this is the actual forecast value based on sales last year

Total Sales LY = 
CALCULATE([Total Sales],
    DATEADD('Date'[Datum],-1,YEAR)
)

After Slicer Total Sales (cum) = 
CALCULATE([Total Sales LY],
    DATESBETWEEN('Date'[Datum],[Min +1 Selected Date],[Max Selected Date])
)

for the scenario consideration, i have values per day for the change in sales revenue (the values can be different for each day and comes from the table DecreaseScenarioMiddle)

these values must have an effect on the calculation after slicer total sales for the scenario

The data model looks like

Thanks for your help to calculate the forecast based on the scenario values

[Melissa]

Hi @Mario,

Try something like this.

After Slicer Total Sales (cum FC) = 
SUMX( 
    DATESBETWEEN('Date'[Datum],[Min +1 Selected Date],[Max Selected Date]), 
    [Total Sales LY] * (1+ CALCULATE( MAX( DecreaseScenarioMiddle[Scenario Decrease] ), TREATAS( VALUES( Dates[Date] ), DecreaseScenarioMiddle[Date] )))
)

However should you need further assistance, provide a sample PBIX. BrianJ did a video on masking your data should sensitivity be an issue.

[Mario]

@Melissa

sampl

Hey @Melissa, thanks for your support. Your Formular don´t itterate the decrease value for each day with the sales but only the max of the decrease values for the sales of each day and ignore the other sceanario decrease values. Do you have a further look to it?

[Melissa]

There was only one value visible in the depicted DecreaseScenarioMiddle, that’s why it’s always appreciated if you supply a small sample PBIX. That will help provide a solution more quickly.

 After Slicer Total Sales (cum FC) v2 = 
    SUMX(
        DATESBETWEEN('Date'[Datum],[Min +1 Selected Date],[Max Selected Date]), 
        [Total Sales LY] * (1+ CALCULATE( 
                LOOKUPVALUE( DecreaseScenarioMiddle[Scenario Decrease] ), DecreaseScenarioMiddle[Date], SELECTEDVALUE( 'Date'[Datum] )), 
                TREATAS( VALUES( 'Date'[Datum] ), DecreaseScenarioMiddle[Date])
            ))
        )

[Mario]

Thanks @Melissa and great solution, this formula works fine and i get the right results