Forum Discussion
Moving Average every three seconds
Hello! I am attempting to find the moving average for every three seconds in unix time. Any suggestions? Here is an example of values I have to work with. Additionally, I am working with a large dataset with multiple other columns.
Hi xsaldana ,
Convert the unix time to datetime.
https://whatthefact.bi/power-bi/dax/convert-unix-timestamp-to-date-or-datetime-format-in-power-bi/
Column = #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [unix time])
Then create a calculated column with below dax formula:
Column = CALCULATE(AVERAGE('Table'[value]),FILTER('Table','Table'[datetime]>EARLIER('Table'[datetime])-3/86400&&'Table'[datetime]<=EARLIER('Table'[datetime])))Best Regards,
Jay
3 Replies
xsaldana , Convert unix time to datetime
power query -https://community.powerbi.com/t5/Desktop/Converting-UNIX-time-to-Date-in-PowerBI-for-Desktop/m-p/130951
Then in DAX have column
averageX(Filter(Table, Table[DateTime] <= earlier(Table[DateTime] ) && Table[DateTime] >= earlier(Table[DateTime] ) -time(0,0,3) ) ,[Value] )
amitchandak
thank you for taking the time to look at this! Unfortunately, this did not work for me. The column processes for a while but doesn't load.
Hi xsaldana ,
Convert the unix time to datetime.
https://whatthefact.bi/power-bi/dax/convert-unix-timestamp-to-date-or-datetime-format-in-power-bi/
Column = #datetime(1970, 1, 1, 0, 0, 0) + #duration(0, 0, 0, [unix time])
Then create a calculated column with below dax formula:
Column = CALCULATE(AVERAGE('Table'[value]),FILTER('Table','Table'[datetime]>EARLIER('Table'[datetime])-3/86400&&'Table'[datetime]<=EARLIER('Table'[datetime])))Best Regards,
Jay
