Sliding window operation

[fabiansd]

Hi!

So I have data registered on datapoints with an assigned column of (date + timestamp). This means that I cannot use the DATE-functions (like DATESBETWEEN) due to duplication errors.

I want to perform a calculation on a data point using the datapoints in the last 24 hours. The resolution on the data is in minutes, so a 24-hour window yields 1440 data points. My first attempt was using a FILTER method to filter out this window for each point iteratively for each datapoint, meaning that a table of 1440 data point is loaded for each data point in the dataset. This operation simply never finishes..

What I think would be the right approach is performing a sliding window calculation. Thus, when you jump to a consecutive data point, you shift the window one time step such that you only have to delete the earliest point and add the point you just visited. This avoids reloading 1440 data points. 

The calculation is counting the number of data points that is below a static limit.

Any suggestions on how I can do this?

[v-jiascu-msft]

Hi @fabiansd,

Can you share a dummy sample that we can download? And the expected result.

Best Regards,
Dale