Forecast based on current YoY growth

DAX DAX Calculations
12

Hello @NickvanDijk,

As suggested earlier, please specify your fixed set of requirements. Since your original query has been resolved please close out this thread. For any new requirements, please create new thread since only question is permissible under one thread. Lastly, before I sign off from this thread. Here’s my final solution on this thread.

When you change the context from “Dates” to “Months” you’ll also be required to adjust your formulas accordingly. Just by replacing the fields into the visualization the context will not change by default.

So since you’re changing the context at a monthly granularity you’ll be required to keep a note that Total Sales and Total Sales LY figure changes i.e. it now shows the figures for the entire month. So since it’s a monthly level granularity the “YoY Growth” changes from “1.13” to “0.81”. Below is the formula provided for the reference. Before that “Fix your totals for Total Sales LY” and than proceed with this -

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

Note: The reason why “YoY Growth” went blank for the subsequent months is because inside “ALLSELECTED()” function I had mentioned “Dates[Dates]” as a reference so when you changed the Dates to Months it went blank. Now, I’ve referenced the entire date table inside the ALLSELECTED().

Once this is done, it’s your choice whether to evaluate the forecast at a “1.13” figure (which is at a daily level granularity) or to evaluate it at a “0.81” figure (which is at a monthly level granularity). Depending upon your further requirements create the formula’s further. Below is the screenshot of the final result provided for the reference -

Final Results
Final Results1372×683 42.4 KB

I’m also attaching the working of the PBIX file for the reference. Also please go throug the education portal where there’re lot’s and lot’s of videos available which explains the concept on context. Also providing few links below of the post which one of our expert @AntrikshSharma has created which explains this concepts.

Hoping this meets your requirements. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

Project A Current Trend Forward Repeatedly - Forecasting Techniques, Power BI - arsh v4.pbix (442.0 KB)

1 Like