Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Rolling Average Quick Measure - Have I done this correctly?

Hi all,

 

I'm using the rolling average quick measure for the first time and I just wanted to sense check that it worked correctly. I need a rolling 7 day average, so do I need to select -6 or -7? And should the second part of the DATEADD(_last date be a 0 or a 1?

 

Spoiler
Count of EventType rolling average =
IF(
ISFILTERED('Date'[Date]),
ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
VAR __LAST_DATE = LASTDATE('Date'[Date].[Date])
RETURN
AVERAGEX(
DATESBETWEEN(
'Date'[Date].[Date],
DATEADD(__LAST_DATE, -7, DAY),
DATEADD(__LAST_DATE, 0, DAY)),
CALCULATE(COUNTA('Agent Data'[EventType]))))

Kind regards,

 

Jordan 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi all,

     

    The answer to my question was to use the below formula, which had a -6 and 0. Indicating that the current day will be used and the previous 6 days will also be added. 

     

    Spoiler
    EventType 7 Day Rolling Average =
    IF(
    ISFILTERED('Date'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __LAST_DATE = LASTDATE('Date'[Date].[Date])
    RETURN
    AVERAGEX(
    DATESBETWEEN(
    'Date'[Date].[Date],
    DATEADD(__LAST_DATE, -6, DAY),
    DATEADD(__LAST_DATE, 0, DAY)
    ),
    CALCULATE(COUNTA('Agent Data'[EventType]))
    ))

     

     

3 Replies

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

    Anonymous , Have you tried like

    Rolling 7 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-7,Day))

    or like

     

    Rolling 3 till last 2 Day= CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],max(dateadd(Sales[Sales Date],-2,Day)),-3,Day))

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Anonymous ,

    You can update the formula of measure [Count of EventType rolling average] as below:

    Count of EventType rolling average =
CALCULATE (
    COUNT ( 'Agent Data'[EventType] ),
    DATESINPERIOD ( 'Date'[Date], LASTDATE ( 'Date'[Date] ), -7, DAY )
)​​

    In addition, you can refer the following documentations to achieve it.

    Rolling Average in Power BI

    Moving average based on Rolling 7 days - per customer/subject

    Moving Average

    Best Regards

    Rena

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi all,

     

    The answer to my question was to use the below formula, which had a -6 and 0. Indicating that the current day will be used and the previous 6 days will also be added. 

     

    Spoiler
    EventType 7 Day Rolling Average =
    IF(
    ISFILTERED('Date'[Date]),
    ERROR("Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column."),
    VAR __LAST_DATE = LASTDATE('Date'[Date].[Date])
    RETURN
    AVERAGEX(
    DATESBETWEEN(
    'Date'[Date].[Date],
    DATEADD(__LAST_DATE, -6, DAY),
    DATEADD(__LAST_DATE, 0, DAY)
    ),
    CALCULATE(COUNTA('Agent Data'[EventType]))
    ))