Forum Discussion
DAX Moving hourly average/count
AlanRGroskreutz, let me know if this is what you're looking for:
rolling entry count = var windowStart = SELECTEDVALUE(results[Entry 20 min Date time bin]) var windowEnd = windowStart + TIME(0, 20, 0) return CALCULATE( COUNT(results[Entry Time]), // After we change filters, do our calculation FILTER( // Replace any existing filter on the [Entry 20 min Date tiem bin] column with one that looks at the period we want ALL(results[Entry 20 min Date time bin]), // Look through *all* values in the column, ignoring any existing filters [Entry 20 min Date time bin] >= windowStart && [Entry 20 min Date time bin] <= windowEnd // Keep those rows which are in our time range ) )Matrix with resultsThe key here is to use ALL to clear any groupings/filters that are applied to look at rows of your table which might be filtered out in the scope that the measure is being executed in.
AlanRGroskreutz, let me know if this is what you're looking for:
rolling entry count =
var windowStart = SELECTEDVALUE(results[Entry 20 min Date time bin])
var windowEnd = windowStart + TIME(0, 20, 0)
return CALCULATE(
COUNT(results[Entry Time]), // After we change filters, do our calculation
FILTER( // Replace any existing filter on the [Entry 20 min Date tiem bin] column with one that looks at the period we want
ALL(results[Entry 20 min Date time bin]), // Look through *all* values in the column, ignoring any existing filters
[Entry 20 min Date time bin] >= windowStart && [Entry 20 min Date time bin] <= windowEnd // Keep those rows which are in our time range
)
)
TaylorClark , Thanks mate, that did it. I had tried an ALL filter before but most likely messed it up because I got nothing.
Thanks for the quick response.Ah, and the SELECTEDVALUE was also key.
Sorry TaylorClark , I found a problem, but not with your code, with an assumption I had made. I was assuming that each sector would have at least one flight entering it in each 20 min period, so when I was creating the 20 min Date-time bins, I was basing it off of the entry times.
The problem that happens is that for the rolling count if you have a period that has 0 entries, it doesn't just use zero for that 20 min bin and count the next two, it just skips that bin as if it didn't exist. Am I going to have to create a 20 min Date-Time bin table to solve this?
