Moving Averages - Relative Data Slicer

[brettmacdonald8]

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 

[amitchandak]

@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.

[CNENFRNL]

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]
)

[daxer-almighty]

I'd strongly and kindly suggest you read about ALLSELECTED. You're not using it properly and don't understand what it's for and how it behaves. Here's an article: The definitive guide to ALLSELECTED - SQLBI