Selecting seasonality for forecasting daily data

[RickScanlon]

Hi have read many great responses on this forum re the forecast option in Power BI but still unable to figure this specific problem type out. 

I have a fact table with revenue per day by many dimensions (so many records per day).  The underlying data exhibits strong seasonality within the week (roughly, weekdays are worth ~1.5x weekend days) and within the year with peak in mid-Sept to mid-Dec (coinciding with year end holidays) and trough in Q1. 

I am using a running total revenue with variable start and end dates. This smooths out the seasonality and has shown to be the most predictive .  For the running total, I have daily data from Jan 1, 2024 - Jul 20, 2024, so 201 days.  I am forecasting days until year end, so 164 days in the forecast period. 

Curious what others would use for seasonality selection.  The actual forecast line is great and compares well with R forecast library results with same dataset.  However, auto produces suspiciously huge upper and lower bounds irrespective of the confidence interval.  The most reasonable results for upper and lower bounds (again comparing to R forecast library results) come with higher seasonality selections (anywhere between 6 to 40 points), with tightest range at 14 points (determined by trial and error).  Trying to reconcile this with the facts of the data set (201 days of historical data, strong weekly seasonality, etc.) and my forecast goal (daily until YE2024).

I also noticed that the function is super senstive to latest date selected in the running total period, which scares me given weekly volatility.  Would greatly appreciate thoughts.

PS, if anyone is very familiar with this feature in Power BI and TS based forecasting, in general, would be thrilled to connect. 

[RickScanlon]

Thanks for the thoughtful response, Carson!  I really appreciate it.  I read those links before posting.  They are all very helpful, especially the first one by Greg Deckler.  I actually used running totals to smooth the data.  It's strange how small changes in seasonality can have such big impacts on the upper and lower bounds, but thankfully not on the main forecast.  I guess the PBI forecast tool is meant to be used as a helpful feature but not as a core tool for heavy forecasting.  I will stick with R for that.  I will also do some generic slope fitting measures for forecasting running totals to make a nice dynamic forecasting tool. 

[v-jtian-msft · Accepted solution]

Hi,@RickScanlon I am glad to help you.
Although I'm not very good at seasonal forecasting as a feature, I found relevant articles and tutorials about it in Power BI that I hope will be helpful to you
URL:
Correlation, Seasonality and Forecasting with Powe... - Microsoft Fabric Community
Solved: Forecast Seasonality - Microsoft Fabric Community
Power BI Forecasting: How To Do It The Right Way? (scaleupally.io)

For the suspiciously large upper and lower bounds you mentioned, this may be due to large fluctuations in the data or some unusual values, which are taken into account when predicting the data. You can try to optimize your model data to remove outliers and make sure that the values are as smooth as possible without very large fluctuations, and it is recommended that you compare your data with a variety of time prediction tools, such as Python's Prophet library, to find the right prediction.
You could try to make some similar test data, or upload your PBIX file without sensitive data to the forum, which will be helpful to help other users to find out the right prediction scheme for your data.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.