Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Devtr
Helper III
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 dateproductqtypriceSold_Amt

 

Products2

product namecategory

 

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.Screenshot 2024-05-13 165539.png

 

Here is the relationships:

Relationships.png

 

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.

1 ACCEPTED SOLUTION

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1715827923876.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

8 REPLIES 8
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

 Product aProduct bProduct c
2023 June21650.0013265.006225.00
2022 June28400.0014950.006300.00
Total50050.0028215.0012525.00
divide by 225025.0014107.506262.50
multiply by 1.0325775.7514530.736450.38
    
Forecast June 202425775.7514530.736450.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.

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1715827923876.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi,

PBI file attached.

Hope this helps.

Ashish_Mathur_0-1715827923876.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you @Ashish_Mathur 

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Devtr
Helper III
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!

v-zhengdxu-msft
Community Support
Community Support

Hi @Devtr 

 

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

vzhengdxumsft_0-1715650191526.png

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.

 

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.