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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
woofwoof123
Frequent Visitor

Rolling average for past 30 days in minutes

How do I create a 30-day rolling average field to account for timestamp (each minute). So far it makes sense for my current rolling average field to show the 30 day rolling average for each date, but not each minute. When I add timestamp to the table, it throws the rolling average off. Any thoughts on how I can do that

 

woofwoof123_1-1662004551254.png

 

 

woofwoof123_2-1662004625504.png

 


Rolling_Average =
VAR NumDays = 30
Var RollingSum =
      CALCULATE(
              SUM(Query1[LAST_FILL]),
DATESINPERIOD(Query1[Date], LASTDATE(Query1[DATE]), -NumDays, DAY), ALL(Query1[Timestamp])
       )
RETURN
RollingSum/NumDays

4 REPLIES 4
daXtreme
Solution Sage
Solution Sage

First off, when using time-intel functions in PBI you have to have a well-formed dates table and you don't. Please consult the docs on DATESINPERIOD. If you follow Best Practices, this might resolve your problem right away. Seriously.

v-rongtiep-msft
Community Support
Community Support

Hi @woofwoof123 ,

I think you can try this code.

Rolling_Average =
CALCULATE (
    AVERAGE ( Query1[LAST_FILL]),
    FILTER (
        ALL ( 'query' ),
        'query'[Date] <= MAX ( 'query'[Date] )
            && 'query'[Date]
                > MAX ( 'query'[Date] ) - 30
    )
)

 

If I have misunderstood your meaning, please provide a pbix file without privacy information and more details with your desired output.

 

Best Regards

Community Support Team _ Polly

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

woofwoof123_0-1662426508064.png

Attaching this image above to use as a "reference". Instead of EMA (Exponential Moving Average), I want just a 30-day moving average vs Intraday data. 

the file is connected to a database connection and is too large to share. But essentially I am trying to have something like a 9am-5pm graph that shows today's "LAST_FILL" and the past 30days rolling average in minutes. Not days... but minutes. Minutes, because the data is in minutes. 

 

woofwoof123_1-1662336391186.png

 

 

woofwoof123_0-1662332087739.png

So I want it to look like LAST_FILLS per minute 9-5 intraday vs. average LAST_FILLS of past 30 days in minutes. 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.