Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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 :
Solved! Go to 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!!
Proud to be a 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!!
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
92 | |
87 | |
84 | |
66 | |
49 |
User | Count |
---|---|
140 | |
114 | |
110 | |
59 | |
59 |