Forum Discussion
Forecasting Sales
Hi guys,
Currently I am trying to build a forecast for sales, but taking the AVG of the past 2 weeks. Note that I'm trying to use a card and not a table ( which is what is blocking me a bit I believe ).
Thing is that item goes out of sales on specific date, and currently I get the forecast based on the avg until the last date.
Here is an example how I think it's working right now :
| item | avg_daily | end_date | Day_from_end | |
| 1 | 50 | 25-Jul | 6 (max(date) | 300 |
| 2 | 75 | 28-Jul | 6 | 450 |
And here how I would want it to work :
| item | avg_daily | end_date | Day_from_end | end_sales |
| 1 | 50 | 25-Jul | 3 | 150 |
| 2 | 75 | 28-Jul | 6 | 450 |
| Total | 600 |
Basically instead of getting the AVG sale for all the items, then multiply that AVG until the the day_from_end based on the max date -> AVG sales by Item, then multiply that AVG until their specific day_from_end (that I get from a measure )
Any idea ?
(currently I get their specific end date with :
Thanks !
Hi LC01 - To forecasting sales based on the average of the past 2 weeks for each item until their specific end date and displaying.
Create a measure to calculate the Average Daily Sales for the Past 2 Weeks
AvgDailySales =
CALCULATE(
AVERAGE('Sales'[SalesAmount]),
DATESINPERIOD(
'Dim Date'[Date],
MAX('Dim Date'[Date]),
-14,
DAY
)
)create another measure to calculate end date from each day:
DaysFromEnd =
DATEDIFF(
TODAY(),
MAX('Sales'[EndDate]),
DAY
)we can use the above calculation for forcasting
TotalForecastedSales =
SUMX(
SUMMARIZE(
'Sales',
'Sales'[Item],
"ItemForecast", [AvgDailySales] * [DaysFromEnd]
),
[ItemForecast]
)Hope it works, check it
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
2 Replies
Hi LC01 - To forecasting sales based on the average of the past 2 weeks for each item until their specific end date and displaying.
Create a measure to calculate the Average Daily Sales for the Past 2 Weeks
AvgDailySales =
CALCULATE(
AVERAGE('Sales'[SalesAmount]),
DATESINPERIOD(
'Dim Date'[Date],
MAX('Dim Date'[Date]),
-14,
DAY
)
)create another measure to calculate end date from each day:
DaysFromEnd =
DATEDIFF(
TODAY(),
MAX('Sales'[EndDate]),
DAY
)we can use the above calculation for forcasting
TotalForecastedSales =
SUMX(
SUMMARIZE(
'Sales',
'Sales'[Item],
"ItemForecast", [AvgDailySales] * [DaysFromEnd]
),
[ItemForecast]
)Hope it works, check it
Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!Thanks after changing it a bit it worked. For example the average wasnt working properly, so I changed for sum & divide by number of days.
