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

New Member

## 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 :

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

Thanks !
1 ACCEPTED SOLUTION
Super User

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!

Proud to be a Super User!

2 REPLIES 2
Super User

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!

Proud to be a Super User!

New Member

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.