How to calculate an average of sales for the last 4 days of a specific day of week

[jessicaf31]

Hi everybody !

I hope I will be clear 🙂

I have a database of all the sales in a restaurant per day (one row correspond to one item ordered so I have a lot of rows for one single day).

I have added a column with the weekday and I know my last day (which corresponds to my max(date) of my last actualization, so now my last day is Thursday March, 3rd -> weekday 4)

I want to compare my sum of sales of my last day, which is Thursday (I have already this measure), to the average of sum of sales for my 4 last Thursdays (not always Thursday).

What I have done for the moment is dividing the sum of my sales for all my thursdays (here 950K) by the number of thursdays from my database (44) but it is obviously not the good average and not what I want at the end...

Thanks a lot for your help ! 🙂

[konstantinos]

@jessicaf31 Correct me if I undesrtand wrong..

You need to compare  i.e. Thursday's sales with the Average daily sales of previous 4 Thursdays ( it can be Monday etc ) right..?

Also assume that you have a Date table with relationship with sales table...and that you use Dates as rows

Not sure if this the optimal way but let's try since it is interesting.

In order to accomplish that you need to create an index  in Dates table for every day in week. First Monday is 1 , Second Monday is 2 etc..

Create a calculate column in Date Table 

Index of same day =
VAR DayOfWeeK = Dates[DayInWeek]

VAR RowDate = Dates[Date]

RETURN
    CALCULATE (
        COUNTROWS ( Dates );
        FILTER ( Dates; Dates[DayInWeek] = DayOfWeeK && Dates[Date] <= RowDate )
    )

Now that we have the index we need to calculate the average of the previous 4 same days

//  Dates[Index of same day] is the calculated column in Dates 
// Dates[DayInWeek] is the number of day in week i.e Monday is 1
//[Total Sales] is the measure for your sales that we average per same 4 days
 

Average 4 weeks same day =
AVERAGEX (
    FILTER (
        ALL ( Dates );
        Dates[Index of same day]
            >= MAX ( Dates[Index of same day] ) - 4  
            && Dates[Index of same day] < MAX ( Dates[Index of same day] )
            && Dates[DayInWeek] = MAX ( Dates[DayInWeek] )
    );
    [Total Sales]
)

We are calculating the average of daily sales (Dates[Date] ) that are between 4 same days before (with  Date

 >= MAX(Dates[Index of same day]) - 4 

 and the date in table row

< MAX ( Dates[Index of same day] )

where all days are the same i.e. Mondays from the DayinWeek = 1 

= MAX ( Dates[DayInWeek] )

Hope that make sense and helps 

edit: some syntax errors , no autocorrect

[jessicaf31 · Accepted solution]

@Greg_Deckler @konstantinos thanks a lot for your answers !

What I have finally done (and is working) is :

• In my table "Transaction", Group by Date in order to have the sum of amount per day

It created a new Table "Transaction (2)".

Once this table is saved and applied, I have added a New Column "DateLess28" which is  = 'Transaction (2)'[Date]-28

And finally I have added a new measure : 

AverageDayLastDay = AVERAGEX(FILTER(ALL('Transaction (2)');'Transaction (2)'[Date]>=MAX('Transaction (2)'[DateLess28]) && 'Transaction (2)'[Date]<MAX('Transaction (2)'[Date]) && WEEKDAY('Transaction (2)'[Date];2)=[JourDernierJour]);'Transaction (2)'[SumCA])

Knowing that my "JourDernierJour" (which means "DayOfTheLastDay") was a measure added and = WEEKDAY(MAXX(ALL('Transaction');'Transaction'[Date]);2)

I am not sure it is the best solution but it works 🙂

Thanks again for your time !!

Jessica

[konstantinos]

@jessicaf31  The approach is similar..

There are fortunately many ways to end in the expected result with PowerBI..

[Greg_Deckler]

@jessicaf31 - Perhaps something like this:

Measure = CALCULATE(AVERAGE([Amount]),FILTER(ALL(RestaurantSales),[Date] >= MAX(Dates[MyDate]) && [Date] < MAX(Dates[Date])))

I have a Dates table with the date and a custom column:

MyDate = [Date] - 28

[jessicaf31]

@Greg_Deckler when I want to add a custom column in my query, it doesn't work : 

A

Moreover, I can't do Average([Amount]) because each amount is very small (one row corresponds to one item ordered, so the amount of each row is about 10€, and what I want is the sum of all the items per day)...

Thanks a lot !