30 Day Rolling Average (Only Weekdays)

[seanpratt]

Might be overthinking this one. I have a 30 day rolling average formula as this:

30DaysThisYear =

CALCULATE(

AVERAGEX(ORDERS,SUM(ORDERS[DIRECT REVENUE])),

DATESINPERIOD('Calendar'[Date], LASTDATE('Calendar'[Date]), -30, DAY))

I have created a calculated column in my calendar table called WeekdayWeekend (It's basically a SWITCH formula saying "If Day of Week = Sunday, "Weekend", etc)... How do I factor that logic in to my 30 day rolling average?

[v-eachen-msft]

Hi @seanpratt ,

I created a flag column to check if it is weekday and created a rank column to calculate 30 weekdays.

Then create a measure to get the rolling average.

AVG30 =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Date] <= MAX ( 'Table'[Date] )
            && 'Table'[flag] = 1
            && 'Table'[rank] <= SELECTEDVALUE ( 'Table'[rank] )
            && 'Table'[rank]
                >= SELECTEDVALUE ( 'Table'[rank] ) - 30
    )
) / 30

 Here is the test file for your reference.

[seanpratt]

@v-eachen-msft 

That's not quite it either. I do have a separate Calendar table so wondering if that's creating a disconnect. I've attached the sample pbix that I'm using. I used your formula and did an audit from mid-May and went back 30 days and the measure was remarkably high compared to what it should've been. 

Also - is it weird that my date hierarchy is skipping days? I know they're skipping the weekends, but it's odd that I'm not seeing just $0 revenue on those days, no? 

sample pbix 

[amitchandak]

@seanpratt , Add workday column in you Calendar table and try

New column

Work Day = if(WEEKDAY([Date],2)>=6,0,1) 

Measure

30DaysThisYear =
CALCULATE(
AVERAGEX(ORDERS,SUM(ORDERS[DIRECT REVENUE])),
DATESINPERIOD('Calendar'[Date], LASTDATE('Calendar'[Date]), -30, DAY), filter('Calendar','Calendar'[work day]=1))

[seanpratt]

Hmm I think that got us farther away from the solution. Now it's just repeating the daily revenue total. It's taken out the week end days as you can see from "Day" but the 30DaysThisYear formula is now no longer finding the average revenue from 30 weekdays ago to today