Rolling Average by Day of Week

[sharmon9000]

Recently I was asked to create a report that shows customers orders counts by day of week.  Being new to PowerBI, and still learning how to use it, I was able to create the start of this report.  The underlying data is very simple containing a few customers, order dates,  and number of orders for that date. 

 

I was able to create a chart in PowerBI in the image above that seems to be working for the current week.  Next I was asked to create a 6 week and 12 week rolling average that would be able to filtered to an earlier week if needed, while then calculating the rolling average from that selected week.  So if someone selected a week-ending date from 2 months ago, it would re-calculate the chart above to show the data from that week and using the 6 week average from that point in time.  I continue to struggle with this concept and dont even know if it is even possible.

My formula for the 6 week rolling average is:

# 6 week Avg = ((CALCULATE(SUM(CustomerList[Orders]), 'Date'[WeekNumber]>=WEEKNUM(TODAY())-6, 'Date'[WeekNumber]<>WEEKNUM(TODAY())))/6)+0

I realize the formula above will not work with a date filter because I am using TODAY() inside of it.   

Searching around it seems I needed a Date Table, which I created and populated with various columns for DayofWeek, DayName of Week, Week Number, WeekEndingDate, etc...

I have included links to the files used in the report below.

Excel Sheet Loaded into PowerBI

PowerBI Report PBIX File

Any help would be greatly appreciated.  Thanks in advance.

-Sean

[v-ljerr-msft · Accepted solution]

Hi @sharmon9000,

If I understand you correctly, you should be able to use the formula below to create a measure to calculate the 6 week rolling average, then use the Date column from the Date table as a Slicer to get the expected result in your scenario. 

# 6 week Avg =
VAR selectedWeekNum =
    MAX ( 'Date'[WeekNumber] )
RETURN
    (
        (
            CALCULATE (
                SUM ( CustomerList[Orders] ),
                FILTER (
                    ALLEXCEPT ( CustomerList, CustomerList[Customer] ),
                    WEEKNUM ( CustomerList[OrderDate] )
                        >= selectedWeekNum - 6
                        && WEEKNUM ( CustomerList[OrderDate] ) < selectedWeekNum
                )
            )
        )
            / 6
    )
        + 0

Regards

[sharmon9000]

Excellent!!! This worked perfectly...I gues I didnt understand the VAR aspect of DAX.  This really helped out alot.  Is there anyway to do this for the day of week (ie. sunday, monday, tuesday..etc) for a selected week?  So it would show the DayName of week, and then that days 6 week average for that specific DayName( the last 6 weeks Wednesdays Average.)  

If not, no worries...I appreciate the answer, it has helped me out alot.

-Sean

[v-ljerr-msft]

Hi @sharmon9000,

---

---

Is there anyway to do this for the day of week (ie. sunday, monday, tuesday..etc) for a selected week?  So it would show the DayName of week, and then that days 6 week average for that specific DayName( the last 6 weeks Wednesdays Average.)  

Could you try the formula below to see if it works? 

# 6 week Avg =
VAR selectedWeekNum =
    MAX ( 'Date'[WeekNumber] )
VAR currentDayName =
    FIRSTNONBLANK ( 'Date'[DayName], 1 )
RETURN
    (
        (
            CALCULATE (
                SUM ( CustomerList[Orders] ),
                FILTER (
                    ALLEXCEPT ( CustomerList, CustomerList[Customer] ),
                    WEEKNUM ( CustomerList[OrderDate] )
                        >= selectedWeekNum - 6
                        && WEEKNUM ( CustomerList[OrderDate] ) < selectedWeekNum
                        && 'Date'[DayName] = currentDayName
                )
            )
        )
            / 6
    )
        + 0

Regards

[Tash_247]

Hi, @v-ljerr-msft 

Please can you assist me.

I have been trying to calculate the Average percentage for the last 5 weeks by the days of the week .

So I need the average for the last 5 Mondays,  last 5 Tues, etc....

I have tried the following but not getting what i expect.

im getting the percentage divided by 5, and not the average for the last 5 weeks.

I have used this thread, what am i doing wrong?

per_active_reg_5_week_avg =
VAR selectedWeekNum =
MAX ( Query1[Week of Year] )
VAR currentDayName =
FIRSTNONBLANK ( Query1[Day Name], 1 )
RETURN
(
(
CALCULATE (
( query1[per_active_reg]),
FILTER (
ALLEXCEPT ( Query1, Query1[trans_date].[Date]),
WEEKNUM ( Query1[trans_date])
>= selectedWeekNum - 5
&& WEEKNUM (Query1[trans_date] ) < selectedWeekNum
&& Query1[Day Name]= currentDayName
)
)
)
/ 5
)
+ 0

[sharmon9000]

Sorry for the delay.  This works perfectly, and I appreciate answering the question off topic.  You have giving me a greater understanding of the VAR usage of the DAX language.  Also, you need to write a book on this...so I can buy it. 🙂

Again, thank you.

-Sean