Forecasting based on x

[lherbert501]

Hi,

I have a model with a fact sales table with a couple of years data and a date dimension, and I'm trying to forecast the next x amount of days/months based on previous. 3 months would be great but I would look to have similar to a what if parameter for the user to say how many days/months they would like to see.

My data is laid out similar to below and is by month/period instead of day by day. Could somebody please help with this?

FactSales

Item -Sales-Period

DimDate

Date - Period-Month

[123abc · Accepted solution]

To forecast sales in Power BI based on historical data and provide flexibility for users to select a forecasting period (e.g., x days or x months), you can follow these steps:

1. Prepare Your Data:

   Make sure your data model in Power BI consists of two tables, as you've described:

   • FactSales: This table should contain the item, sales, and period (in months, it seems).

   • DimDate: This table should contain the date and period-month.

2. Create a Date Table:

   Ensure your DimDate table is set up as a proper date dimension table. You can create a relationship between this date table and your FactSales table using the 'Date' or 'Period' columns.

3. Define Forecast Period:

   You need a way for users to specify the forecasting period (x days or x months). You can create a parameter to allow users to input this value.

   • Go to 'Modeling' in Power BI.
   • Click on 'New Parameter.'
   • Define a parameter name, data type, and range that allows users to select a number of days or months.

4. Create a Measure for Forecasting:

   Next, you'll create a measure to calculate the forecast. This measure will use the DAX language. The specific formula will depend on your forecasting method (e.g., simple moving average, exponential smoothing, etc.). Let's assume you're using a simple moving average:

Forecast Sales =
VAR SelectedPeriod = [Parameter].[Value] // Replace [Parameter] with the actual parameter name
VAR LastDate = MAX(DimDate[Date])
VAR StartDate = LastDate - SelectedPeriod
VAR SalesInSelectedPeriod =
CALCULATE(SUM(FactSales[Sales]),
DimDate[Date] >= StartDate && DimDate[Date] <= LastDate)
RETURN
DIVIDE(SalesInSelectedPeriod, SelectedPeriod)

1. This measure calculates the average sales in the selected period based on the user's input.

2. Create Visuals:

   Now, you can create visuals in Power BI that display the forecasted sales. You can use a card or line chart to show the forecast.

3. Interactivity:

   Make sure to set up interactivity. When the user changes the parameter value, the forecast should adjust accordingly.

4. Publish and Share:

   Once you have created the desired visuals and interactivity, publish your Power BI report to your preferred platform for sharing with others.

Remember that forecasting accuracy depends on the chosen forecasting method and the quality of your historical data. It's important to choose a suitable method for your specific use case and regularly update your data to maintain the accuracy of the forecasts.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.