Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
LC01
Frequent Visitor

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 : 

itemavg_dailyend_dateDay_from_end
15025-Jul6 (max(date)300
27528-Jul6450

 

 

And here how I would want it to work : 

itemavg_dailyend_dateDay_from_endend_sales
15025-Jul3150
27528-Jul6450
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
rajendraongole1
Super User
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!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





View solution in original post

2 REPLIES 2
rajendraongole1
Super User
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!
Appreciate your Kudos!!





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





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.

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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