Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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.
@amitchandak , @Greg_Deckler , @johnt75 , @Ashish_Mathur , @Ritaf1983 , @Ahmedx , @lbendlin,@Ritaf1983
I appreciate any help you can provide.
Thank you.
Solved! Go to Solution.
Hi,
PBI file attached.
Hope this helps.
Hi,
I cannot understand your question. Show the expected result in the PBI file.
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.
You are welcome.
@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!
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.
User | Count |
---|---|
132 | |
74 | |
70 | |
58 | |
54 |
User | Count |
---|---|
192 | |
96 | |
67 | |
64 | |
54 |