Forum Discussion
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!
- Anonymous3 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
- daXtremeSolution 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- AnonymousNot 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.
- AnonymousNot applicable
Also this particular line gives me an error.
if( COUNTROWS( MomentsToAvgOver ) = DeltaT,
- AnonymousNot 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_DecklerCommunity 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.