cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kernow_pasty Frequent Visitor
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

 

2019-02-14 08_31_59-MODEL Prep and Defrost - Power BI Desktop.png

 

 

 

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!

1 REPLY 1
Community Support Team
Community Support Team

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

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.

 

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.

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors