cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper III

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

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 =
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.

Thank you.

1 ACCEPTED SOLUTION
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
8 REPLIES 8
Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III
 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.

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.

Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

PBI file attached.

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

Thank you @Ashish_Mathur

Super User

You are welcome.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Helper III

@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!

Community Support

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.