Forecast based on current YoY growth

DAX DAX Calculations
10

Hello @NickvanDijk,

Ok, so here’s the reason why that “YoY Growth” figure went negative and Forecast started to show vague results when future dates were selected. I’ve used the “Measure Branching” technique here to resolve this issue rather than writing complex formula. Let’s analyze this step-by-step. -

1). We’ve Sales till 6th Jan, 2021 so the sales measure is like this.

Total Sales = 
SUMX( Sales , 
    Sales[Quantity] * RELATED( 'Product'[Current Price] ) )

2). Now, to calculate the Total Sales Last Year. This is formula which we use in normal circumstances -

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

Now, if you observe when slicer is selected for future dates i.e. after 6th Jan, 2021. We don’t have Sales for the current year but it’ll show the Sales figure of the Last Year. Below is the screenshot provided for the reference -

Sales Current Year and Last Year

3). Since we want to calculate the “YoY Growth” where there’s a Sales for the current year and than we want to comapre that with the last year. The formula for “Total Sales LY” should have been like this -

Total Sales LY - 2 = 
IF( ISBLANK( [Total Sales] ) , 
    BLANK() ,
    CALCULATE( [Total Sales] , 
        DATEADD( Dates[Date] , -1 , YEAR ) ) )

Now, if we check the results see what happens -

Revised Total Sales Last Year
Revised Total Sales Last Year832×379 15 KB

If you observe carefully, although now we’ve “Total Sales LY - 2” till 6th Jan’2021 the grand totals show incorrect results i.e. they are still showing the results till 10th Jan, 2021. And this is where the things started to go downhill. So firstly, now, we’ll have to correct the grand total of the “Total Sales Last Year - 2” measure.

4). To fix the grand totals of the “Total Sales LY - 2”. Below is the measure provided for the reference -

Total Sales LY - Totals = 
SUMX(
    SUMMARIZE(
        Dates , 
        Dates[Date] , 
        "@Totals" , 
        [Total Sales LY - 2] ) ,
    [@Totals]
)

Note: To fix the totals, one of our expert @Greg has already created a magnificent post which covers everything about how to fix it. Below is the link provided for the reference.

Now, see the results after writing this measure -

Correct Results of Total Sales LY
Correct Results of Total Sales LY963×346 17.3 KB

5). Now, let’s move ahead with the “YoY Growth” measure -

So logic of YoY Growth is like this -

YoY Growth = DIVIDE( Current_Year_Figures - Last_Year_Figures , Last_Year_Figures , 0 )

Since our last year figures i.e. “Total Sales LY” figures were incorrect. Current Year figures were getting wrongly subtracted in the “Numerator” and were also getting wrongly divided into the denominator as well. So since we’ve already corrected our “Total Sales LY” by writing the above formula. The “YoY Growth” measure will be like this -

YoY Growth = 
CALCULATE( 
    DIVIDE( 
       [Total Sales] - [Total Sales LY - Totals] , 
       [Total Sales LY - Totals] , 
       0 ) , 
           ALLSELECTED( Dates[Date] ) )

Now, let’s check the results of the “YoY Growth” measure. Below is the screenshot provided for the reference

YoY Growth results
YoY Growth results1036×346 20.1 KB

So if you see now, although slicer is selected till 10th Jan, 2021 the results are evaluated and shown as per the last sales figure which happened on 6th Jan, 2021.

6). Lastly, once this problem is also fixed then we’ll write the final measure which pertains to the “Forecasting”. Below is the measure provided for the reference (The “YoY Growth” measure will be multiplied with the original measure of “Total Sales LY” since we want to forecast for future dates and “Total Sales LY” measure provides the results for the future dates.) -

2018 Forecast = 
CALCULATE( [Total Sales LY - 1] ,
    FILTER( Dates, Dates[Year] = "2018" ) ) +
CALCULATE( [Total Sales LY - 1] , 
    FILTER( Dates, Dates[Year] = "2018" ) ) * [YoY Growth]

See the results now. Below is the screenshot provided for the reference -

Final Results
Final Results1142×705 48.9 KB

So all-in-all while evaluating this results several fundamentals were tested at each step.

I’m also attaching the working of the PBIX file for the reference as well.

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Note: If you want the “Average YoY Growth” than follow the same steps to achieve that result as well.

Thanks and Warm Regards,
Harsh

Project A Current Trend Forward Repeatedly - Forecasting Techniques, Power BI - v3.pbix (409.8 KB)

1 Like