Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all,
I'm having a terrible time trying to get my time span filter working for a moving count. I am working on an Air Traffic Predictor model that we'll show the occupancy of air traffic sectors. The customer wants these occupancy counts cut up into 20 minute intervals, but for each interval show the count of that interval and the next two in the future (one hour's worth in total). I know, it makes no sense to me either.
The problem is that whatever measure I try to implement, I always only come out with either the count for that 20 min interval or nothing.
I have tried this:
rolling entry count =
VAR HourFromNow =
NOW() + TIME(1;0;0)
VAR NextHour =
FILTER(
results;
results[Entry 20 Minute date-time bin] <= HourFromNow
)
VAR NextHourCount =
COUNTX(
NextHour;
REsults[Entry Time]
)
RETURN
NextHourCount
from this answer https://community.powerbi.com/t5/Desktop/Moving-Average-for-the-last-3-hours/m-p/980930/highlight/tr...>
That just gives me the same as if I had written
COUNTX('Results'[Entry Time])
I have tried replacing NOW() with MAX(results[Entry 20 Minute date-time bin]) and the same results.
I wanted to include the .pbix file, but I don't know how, so here is a sample of the table with the pertinent columns.
Date | Sector | Flight ID | Entry Time | Entry 20 min bin | Entry 20 min Date time bin |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 07:46:45 | 1899-12-30 07:40:00 | 04/05/2020 7:40 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 08:17:22 | 1899-12-30 08:00:00 | 04/05/2020 8:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 10:10:40 | 1899-12-30 10:00:00 | 04/05/2020 10:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 12:11:21 | 1899-12-30 12:00:00 | 04/05/2020 12:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 15:08:05 | 1899-12-30 15:00:00 | 04/05/2020 15:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 15:18:41 | 1899-12-30 15:00:00 | 04/05/2020 15:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 15:44:22 | 1899-12-30 15:40:00 | 04/05/2020 15:40 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 15:45:39 | 1899-12-30 15:40:00 | 04/05/2020 15:40 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 16:03:09 | 1899-12-30 16:00:00 | 04/05/2020 16:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 16:13:22 | 1899-12-30 16:00:00 | 04/05/2020 16:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 16:22:42 | 1899-12-30 16:20:00 | 04/05/2020 16:20 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 17:39:47 | 1899-12-30 17:20:00 | 04/05/2020 17:20 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 18:02:58 | 1899-12-30 18:00:00 | 04/05/2020 18:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 18:10:10 | 1899-12-30 18:00:00 | 04/05/2020 18:00 |
04/05/2020 0:00 | GCACARR | 1 | 1899-12-30 18:31:46 | 1899-12-30 18:20:00 | 04/05/2020 18:20 |
04/05/2020 0:00 | GCCAACC | 1 | 1899-12-30 08:07:36 | 1899-12-30 08:00:00 | 04/05/2020 8:00 |
04/05/2020 0:00 | GCCAACC | 1 | 1899-12-30 11:55:10 | 1899-12-30 11:40:00 | 04/05/2020 11:40 |
04/05/2020 0:00 | GCCAACC | 1 | 1899-12-30 14:58:18 | 1899-12-30 14:40:00 | 04/05/2020 14:40 |
04/05/2020 0:00 | GCCAACC | 1 | 1899-12-30 15:08:44 | 1899-12-30 15:00:00 | 04/05/2020 15:00 |
04/05/2020 0:00 | GCCAACC | 1 | 1899-12-30 15:35:06 | 1899-12-30 15:20:00 | 04/05/2020 15:20 |
04/05/2020 0:00 | GCCAACC | 1 | 1899-12-30 15:54:17 | 1899-12-30 15:40:00 | 04/05/2020 15:40 |
Any help is appreciated.
Solved! Go to Solution.
@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
)
)
@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?
Yup, making a separate table with all the Date-Tim bins was the solution. I then had to replace the 'result' Date time bin references with references to the new table.
Thanks again.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |