Custom rolling average on time series

[Anonymous]

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 · Accepted solution]

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.

[daXtreme]

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

Also this particular line gives me an error.

    if( COUNTROWS( MomentsToAvgOver ) = DeltaT,

[Anonymous]

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.

[Greg_Deckler]

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