Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Custom rolling average on time series

Hello,

I am looking to write a simple script to create a column for a custom rolling average on time series shorter than a day.  Ideally I want to be able to perform a 10 min, 20 min, or a 40 min time average but I am having difficulties with the DAX syntax.  In Pandas the .rolling() method works really well but haven't been able to figure out something equivalent here.  I wrote a prototype script below that works for days but can't figure out how to make this for something shorter than 24 hours.  Any help is greatly appreciated.  Thank you!

 

mvavg =
VAR _delta_t = 20 // Minutes for rolling average
VAR _StartDate = ???
VAR _EndDate = ???
CALCULATE(
    AVERAGEX(
        ALLSELECTED('TABLE'),
        'TABLE'[variable]
    ),
    DATESBETWEEN(
        'TABLE'[Timestamp],
        _StartDate,
        _EndDate
    )
)
  • Anonymous's avatar
    Anonymous
    3 years ago

    Thank you both.  In the end I processed the original CSV file with Pandas and exported it as a new CSV file that I could then import into PowerBI.  Sorry DAX is very clunky and doesn't compare to Pandas but PowerBI is nice for the aggregations and shared displays/dashboards on the cloud.

5 Replies

  • daXtreme's avatar
    daXtreme
    Solution Sage
    // First of all, you should have a table,
    // call it TimeAxis, that stores your moments
    // in time. Let's say it's in minutes. But such
    // minutes must be unique across the whole
    // time span. So, there must be a unique
    // TimeID field (integer, say, that goes
    // up by 1 - very important), so that you're
    // able to move through the minutes by doing
    // simple arithmetic. Connect this table to
    // your fact table on TimeID.
    
    // Then you write:
    
    mvavg =
    VAR DeltaT = 20 // Minutes for rolling average
    VAR StartMoment = MAX( TimeAxis[TimeID] )
    VAR EndMoment = StartMoment - (DeltaT - 1)
    var MomentsToAvgOver =
        CALCULATETABLE(
            FILTER(
                VALUES( TimeAxis[TimeID] ),
                TimeAxis[TimeID] >= StartMoment
                &&
                TimeAxis[TimeID] <= EndMoment
            ),
            ALLSELECTED( 'FactTable' )
        )
    var Output =
        // This condition makes sure that there are
        // in fact DeltaT moments and not fewer.
        if( COUNTROWS( MomentsToAvgOver ) = DeltaT,
            CALCULATE(
                AVERAGE( 'FactTable'[Measurement] ),
                MomentsToAvgOver,
                ALLSELECTED( 'FactTable' )
            )
        )
    RETURN
        Output
    • Anonymous's avatar
      Anonymous
      Not applicable

      Thank you for the suggestions.  I have an Index column that is ordered with time (same as a DataFrame) but unfortunately the time steps are not homogeneous and I can't simply use the conditions provided.  I was hoping to find a way to set a condition based on time elapsed (I have a column for that) and aggregate enough rows to meet the rolling average requirement.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Also this particular line gives me an error.

       

          if( COUNTROWS( MomentsToAvgOver ) = DeltaT,
  • Anonymous's avatar
    Anonymous
    Not applicable

    Thank you both.  In the end I processed the original CSV file with Pandas and exported it as a new CSV file that I could then import into PowerBI.  Sorry DAX is very clunky and doesn't compare to Pandas but PowerBI is nice for the aggregations and shared displays/dashboards on the cloud.

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion

    Anonymous Datetimes are just decimal numbers with the integer portion as the # of days since 12/30/1899 and the decimal portion is the fraction of a day 1/24/60/60 for example.