Forecasting Sales

[LC01]

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 : 

MAXX(TOPN(1,'Dim Date','Dim Date [DateTime],),'Dim Date [DateTime])

Thanks !

[rajendraongole1 · Accepted solution]

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

[LC01]

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.