Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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 

1 ACCEPTED SOLUTION
Anonymous
Not applicable

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

 

 

View solution in original post

3 REPLIES 3
Anonymous
Not applicable

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

 

 

v-yiruan-msft
Community Support
Community Support

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

 

 

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
amitchandak
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.