Forum Discussion

brettmacdonald8's avatar
brettmacdonald8
New Member
5 years ago

Moving Averages - Relative Data Slicer

Hello - I am trying to use a DAX function to calculate the rolling average of Google Analytics sessions with a custom date range. I use the following function:

 

Rolling Average Sessions =
 AVERAGEX(
        FILTER( ALLSELECTED('Date Functions'[Date]),
                 'Date Functions'[Date] <= MAX('Date Functions'[Date])),
                           'Date Functions'[Sessions])
 
I assume the issue has something to do with having several instances of the same date because I believe these are hourly session totals. Any help would be much appreciated 
 
 

3 Replies

  • CNENFRNL's avatar
    CNENFRNL
    Icon for Community Champion rankCommunity Champion
    5 years ago

    Hi, brettmacdonald8 , you might want to try this formula in a calculated column

     

    Rolling Average Sessions =
AVERAGEX (
    FILTER (
        ALL ( 'Date Functions'[Date] ),
        'Date Functions'[Date] <= EARLIER ( 'Date Functions'[Date] )
    ),
    'Date Functions'[Sessions]
)

     

     

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

    brettmacdonald8 , Seems like you are creating like a column. For new column try like

    Rolling Average Sessions =
    AVERAGEX(
    FILTER( 'Date Functions',
    'Date Functions'[Date] <= earlier('Date Functions'[Date])),
    'Date Functions'[Sessions])

     

    For new measure

     

    Rolling Average Sessions =
    AVERAGEX(
    FILTER( ALLSELECTED('Date Functions'),
    'Date Functions'[Date] <= MAX('Date Functions'[Date])),
    'Date Functions'[Sessions])

     

    or better to use a separate date table

     

    Rolling Average Sessions =
    AVERAGEX(
    FILTER( ALLSELECTED('Date Functions'),
    'Date Functions'[Date] <= MAX('Date Functions'[Date])),
    'Date Functions'[Sessions])

     

    Rolling Average Sessions =
    AVERAGEX(
    FILTER( ALLSELECTED('Date'),
    'Date'[Date] <= MAX('Date'[Date])),
    'Date Functions'[Sessions])

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.