Average Item Sales over last 13 weeks, then forecast ahead

[Anonymous]

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],
                'fact RestaurantSalesTransaction'[MenuItemKey]
            )
        ),
        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!

[v-piga-msft]

Hi @Anonymous ,

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