Sales forecasting for the months in the current year that includes products (using DAX)

[Devtr]

The sales-related dataset has two simple tables.

Sales1

sales date

product

qty

price

Sold_Amt

Products2

product name

category

The Sales1 table has sales data from Jan 2022 to May 2024.

I created a calculated Calendar table.

I am trying to calculate sales forecasting for 2024 (including May 2024), including products dynamically.

This is what I did and it is not working.

Measures:

TOTAL_SALES = SUM(Sales1[sales date])

SALES_LY = CALCULATE([TOTAL_SALES], SAMEPERIODLASTYEAR('Calendar'[sales date]))

SALES_2Y = CALCULATE([TOTAL_SALES],DATEADD('Calendar'[sales date],-2,YEAR))

SALES_3Y = CALCULATE([TOTAL_SALES],DATEADD('Calendar'[sales date],-3,YEAR))

SALES_FORECAST = 
VAR _Growth_Rate = 1.03

RETURN
DIVIDE(Sales1[SALES_LY] + Sales1[SALES_2Y] + Sales1[SALES_3Y],3,0) * _Growth_Rate

I created a calculated table Forecast_Sales_Dates that contains all the dates ending in 12/31/2024. I created a relationship with the Calendar table using the Date column as well:

Forecast_Sales_Dates = 
VAR _First_Date = MIN('Calendar'[sales date])
VAR _Last_Date = MAX('Calendar'[sales date])

RETURN
CALENDAR(
    DATE(YEAR(_First_Date),MONTH(_First_Date),1),
    DATE(YEAR(_Last_Date),12,31)
)

I added some calculated columns for month and year in Forecast_Sales_Dates.

Forecast_Month = MONTH(Forecast_Sales_Dates[Date])

Forecast_Month_Name = FORMAT(Forecast_Sales_Dates[Date],"MMMM")

Forecast_Month_Year = Forecast_Sales_Dates[Forecast_Month_Name] & "-" & Forecast_Sales_Dates[Forecast_Year]

Forecast_Year = YEAR(Forecast_Sales_Dates[Date])

I created another calculated table Forecast using the CROSSJOIN and SALES_FORECAST measure:

Forecast = 
ADDCOLUMNS(
    CROSSJOIN(Forecast_Sales_Dates,Products2),
    "Forecast Sales", [SALES_FORECAST]
)

I added a relationship with the Products2 table.

In the table visual (having 2024 selected in the slicer), the forecast sales column stops at the last actual sales date.

Here is the relationships:

How do I show the rest of the months with the forecast?

The forecast formula is an average of historical data with a growth rate of 3%:

((2024 sales + 2023 sales + 2022 sales) / 3) * 1.03

Here is the PBix file.

Sales.PBix 

@amitchandak , @Greg_Deckler , @johnt75 , @Ashish_Mathur , @Ritaf1983 , @Ahmedx , @lbendlin,@Ritaf1983

I appreciate any help you can provide.

Thank you.

[Ashish_Mathur]

Hi,

I cannot understand your question.  Show the expected result in the PBI file.

[Devtr]

	Product a	Product b	Product c
2023 June	21650.00	13265.00	6225.00
2022 June	28400.00	14950.00	6300.00
Total	50050.00	28215.00	12525.00
divide by 2	25025.00	14107.50	6262.50
multiply by 1.03	25775.75	14530.73	6450.38
Forecast June 2024	25775.75	14530.73	6450.38

Thank you for considering this!

The formula for forecasting the rest of 2024 for each month per product is:

((2023 month + 2022 month) / 2) * 1.03

The above table has all three products a, b, and c total sales for June 2022 and 2023.

The forecast for June 2024 came to 25775.75, 14530.73, and 6450.38 per product respectively.

Here is the link to download the Excel sales file.

How do I apply this logic using DAX dynamically from May 2024 (current month) to Dec 2024?

I appreciate your time and knowledge.

Thank you.

[Ashish_Mathur]

Hi,

PBI file attached.

Hope this helps.

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Devtr]

Thank you @Ashish_Mathur 

[Ashish_Mathur]

You are welcome.

[Devtr]

@v-zhengdxu-msft That was my mistake! After fixing it, the issue is still there. The visual doesn't show the rest of the months!

[v-zhengdxu-msft]

Hi @Devtr 

Actually, I don't even understand that why use date to calculate sale?

I speculate that change the [Sales date] into [sales amt], the formula should work.

Best Regards

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