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?
Example tables below, based on 1 Dish over 5 Fridays - you can see we sold
Lunch - 5
Dinner - 27
Lunch - 2
Dinner - 17
Lunch - 7
Dinner - 22
Lunch - 8
Dinner - 16
Lunch - 10
Dinner - 19
Therefore the average over 5 weeks for:
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 =
SUM('fact RestaurantSalesTransaction'[# Item Qty]),
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 =
[# Item Qty Cumm],
LASTDATE ( 'fact RestaurantSalesTransaction'[CheckOpenDate] ),
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!
Hi @kernow_pasty ,
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.
Join us in the third Triple A event!
It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.
Make sure you didn't miss any of the things that happened in the community in January!