How I Handled Monthly and Weekly Sales Forecasting in Power BI

Recently, I worked on a project where the client provided daily sales data in Excel. Alongside the actual sales figures, they had a forecasted sales amount for each week and month. The task was to create a dashboard in Power BI that would display monthly sales, the monthly forecast, and the difference between actuals and forecasts. Additionally, the client wanted to track these comparisons both by week and by month, which introduced a unique challenge: how to incorporate the monthly forecast into the daily and weekly breakdowns.

To achieve this, I created a separate forecast table similar to a calendar table. This table, named “Forecast Amount,” held the monthly forecast amounts for each month of the year. Then, I divided the monthly forecast by the number of days in each month to generate a daily forecast. By aggregating this daily forecast, I could easily analyze both weekly and monthly performance in Power BI, allowing for a seamless comparison between actual sales and forecasted amounts.

A Possible Improvement

One way to enhance this approach could be to automate the forecast breakdown by using Power Query. Power Query can dynamically calculate the daily forecast based on the monthly forecast, updating automatically if the monthly forecast values change. This way, you could avoid manually updating the forecast table for each month and allow the data model to be more flexible and responsive to changes in forecast figures.

---

Here’s a detailed breakdown of the steps for setting up a dynamic monthly and weekly sales forecast in Power BI using Power Query and DAX.

---

Step 1: Import Sales Data and Forecast Data

1. Import Sales Data: Load your daily sales data from the Excel file into Power BI, ensuring that each row contains at least a date and a sales amount.
2. Import Forecast Data: Load the forecast data. If the forecast is provided monthly (one row per month with the forecasted amount), it should contain:
   • Month and Year
   • Monthly Forecast Amount
   Ideally, this table is in a format where each row represents a month with its respective forecast amount.

---

Step 2: Create a Date Table

1. Go to Modeling > New Table and create a calendar table if you don’t already have one. DAXCopy codeDateTable = CALENDAR(MIN(Sales[Date]), MAX(Sales[Date]))
2. Add calculated columns for Month, Year, Week Number, and Day to enable monthly and weekly aggregations.

---

Step 3: Transform Monthly Forecast into Daily Forecast

1. In Power Query, go to the forecast data table and perform the following steps:
   • Expand to Daily Level: Duplicate each row for the number of days in the month so that each month’s forecast can be spread across individual days.
     1. Add Column > Custom Column: Use a formula to generate a list of dates based on each month, e.g., List.Dates.
     2. Expand this list to create a row for each date.
   • Divide Monthly Forecast by Days in Month:
     1. Create a custom column that divides the monthly forecast amount by the number of days in the month (e.g., ForecastAmount / DaysInMonth).
     2. This gives a daily forecast amount for each day of the month.
2. Load the transformed forecast table back into Power BI.

---

Step 4: Link Forecast Table to Date Table

1. In the Model View, create a relationship between the Date column in your DateTable and the date column in your Forecast table.
2. Make sure you have relationships in place between Sales and DateTable as well, enabling comparisons on common date values.

---

Step 5: Create Measures for Forecast and Actual Sales Comparison

1. Monthly Forecast:
   • Create a measure to calculate the monthly forecast sum:DAX code MonthlyForecast = SUM(Forecast[DailyForecastAmount])
2. Actual Sales:
   • Create a measure for the actual monthly sales: DAX code MonthlySales = SUM(Sales[SalesAmount])
3. Difference:
   • Calculate the variance between the forecast and actual sales:DAXCopy codeMonthlyVariance = [MonthlySales] - [MonthlyForecast]
4. Weekly Forecast and Actual:
   • If weekly comparisons are needed, create similar measures, using SUMX or CALCULATE with WEEKNUM to filter and aggregate data by week.

---

Step 6: Build the Dashboard

1. Use line and bar charts to display actual sales and forecasted sales for each month.
2. Add a variance column chart to visualize the difference between actual and forecasted values.
3. Add slicers for month and year, allowing users to drill down to specific periods.

---

Automate Updates

When using Power Query, your forecast table will automatically update if the client updates the monthly forecast values in the Excel sheet. This avoids manual adjustments and keeps your dashboard flexible.

---

By following these steps, you’ll have a robust setup in Power BI to dynamically track monthly and weekly performance against forecasted sales, and your dashboard will remain up-to-date as new forecast data is provided.