topic Re: Moving Averages - Relative Data Slicer in DAX Commands and Tips

Moving Averages - Relative Data Slicer

brettmacdonald8 — Sun, 13 Dec 2020 06:03:47 GMT

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

Re: Moving Averages - Relative Data Slicer

daxer-almighty — Sun, 13 Dec 2020 09:39:01 GMT

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

Re: Moving Averages - Relative Data Slicer

CNENFRNL — Mon, 14 Dec 2020 03:28:49 GMT

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

Re: Moving Averages - Relative Data Slicer

amitchandak — Mon, 14 Dec 2020 02:42:25 GMT

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.