cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sharmon9000 Frequent Visitor
Frequent Visitor

Rolling Average by Day of Week

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. 

 

 Capture.PNGCapture.PNG

 

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

Re: Rolling Average by Day of Week

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. Smiley Happy

# 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

4 REPLIES 4
v-ljerr-msft Super Contributor
Super Contributor

Re: Rolling Average by Day of Week

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. Smiley Happy

# 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 Frequent Visitor
Frequent Visitor

Re: Rolling Average by Day of Week

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 Super Contributor
Super Contributor

Re: Rolling Average by Day of Week

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? Smiley Happy

# 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

Highlighted
sharmon9000 Frequent Visitor
Frequent Visitor

Re: Rolling Average by Day of Week

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. Smiley Happy

 

Again, thank you.

 

-Sean