Forum Discussion

seanpratt's avatar
seanpratt
Icon for Helper III rankHelper III
6 years ago

30 Day Rolling Average (Only Weekdays)

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?
 
 
Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      seanpratt
      Icon for Helper III rankHelper III
      6 years ago

      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

       

       

       

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      seanpratt
      Icon for Helper III rankHelper III
      6 years ago

      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