cancel
Showing results for
Did you mean:
Frequent Visitor

## Average Item Sales over last 13 weeks, then forecast ahead

Hi All, so I'm getting close but no cigar with this one.

Probelm - Restaurant sells Items/Dishes every day - I want to be able to take the average item sales over the last 13 weeks by Day/Session (Lunch/Dinner) - then use that to predict forward.

Example - today is Wednesday, this coming Friday Dinner - based on the last 13 weeks we sold 5 Steaks on average - therefore we should prepare to sell 5 this coming Friday.

After searching and trying everything from the community over the last 4 days, no luck. I think the challenge is it needs to evaluate dwon through the Month/Week/Day/Session hierarchy?

Any ideas?

Example tables below, based on 1 Dish over 5 Fridays - you can see we sold

Week 45

Lunch - 5

Dinner - 27

Week 46

Lunch - 2

Dinner - 17

Week 47

Lunch - 7

Dinner - 22

Week 48

Lunch - 8

Dinner - 16

Week 49

Lunch - 10

Dinner - 19

Therefore the average over 5 weeks for:

• Lunch should be 5+2+7+8+10= 32 / 5 = 6.4
• Dinner should be 27+17+22+16+19 = 101 / 5 = 20.2
• Whole Day should be 32+101 = 133 / 5 = 26.6 items to make ready for the day

You can see my failed attempts in the middle of the left table, and the measures below. None of the attempts have a grand total of 26.6

Sum the items from the fact

`# Dishes = CALCULATE(SUM('fact RestaurantSalesTransaction'[# Item Qty]))`

Get the running total / cumulative item sales from the fact

```# Item Qty Cumm =
CALCULATE(
SUM('fact RestaurantSalesTransaction'[# Item Qty]),
FILTER(
ALLSELECTED('OpsBI vwDate'[Date]),
ISONORAFTER('OpsBI vwDate'[Date], MAX('OpsBI vwDate'[Date]), DESC)
)
)```

I was then thinking this will find the right running total from the fact, but i dont think this is correct, as its taking the

```# Dishes L13 Weeks =
CALCULATE (
CALCULATE (
CALCULATE (
[# Item Qty Cumm],
GROUPBY (
'fact RestaurantSalesTransaction',
'OpsBI vwTime'[Session],
'OpsBI vwDate'[Day],
'OpsBI vwDate'[FinancialWeekStartDate],
'OpsBI vwDate'[Month],
'OpsBI vwDate'[FinancialYear],
)
),
DATESINPERIOD (
'fact RestaurantSalesTransaction'[CheckOpenDate],
LASTDATE ( 'fact RestaurantSalesTransaction'[CheckOpenDate] ),
-91,
DAY
)
)
)```

Then was trying to divide down by the # distint dates

```# Distinct Dates = CALCULATE(
DISTINCTCOUNT('OpsBI vwDate'[Date]), FILTER('fact RestaurantSalesTransaction', 'fact RestaurantSalesTransaction'[# Item Qty]>=0))```

Then tried an average

`# Avg L13 Weeks = DIVIDE([# Dishes L13 Weeks] , 13)`

Then tried an averagex - this one is the one i though was most accurate, but can't quite get correct

`# Avg Prep = AVERAGEX('fact RestaurantSalesTransaction',SUMX('fact RestaurantSalesTransaction',[# Dishes L13 Weeks]))`

Any help would be greatly appreciated!

Community Support Team

## Re: Average Item Sales over last 13 weeks, then forecast ahead

Based on your information, if I understand your scenario correctly that your formula of # Avg Prep is not calcualted your desired output.

If  it is convenient, could you share the sample data and your desired output so that I could hava a test with your data and help further on it.

Best  Regards,

Cherry

Community Support Team _ Cherry Gao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Announcements