Help with Rolling 50-minute count measure

[Jay_Wang]

Hi there,

I need help with calculating and plotting rolling 60 minute counts of the occurrance of "Arrived" in the table "Arrivals" in the attache PBIX. I have searched and experimented a number of ways following posts here and elsewhere, but failed to achieve what I wanted.

In the attached PBIX, I created a dim table called "Time5", where "Actual Time" are in 5 minute intervals, and "5MIN_Bin" is from 0-287. The "5MIN_Bin" column was also added in the "Arrivals" table based on the "Arrived" time, and linked to the "5MIN_Bin" in the "Time5" table. I then created a measure called "Rolling

60MIN Count" as follows:

Rolling 60MIN Count =

var this5MIN = Max(Time5[5MIN_Bin])

return

CALCULATE(count(Arrivals[5Min_Bin]), FILTER(ALL(Time5[5Min_Bin]), Time5[5Min_Bin]<=this5MIN && Time5[5Min_Bin]>this5MIN-12))

I plotted this "Rolling 60MIN Count" against the "Actual Time" in a line chart and got this:

which did not do the rolling count in 60 minutes (counts the last 12 5-minute intervals) at all, where the highs should be in the ~40s instead of 5, and the curve should be more smooth. Could someone please let me know what I did wrong with the DAX? I also tried the RANKX method to add a "Rank_Time" row in the "Time5" table, and used that to create the measure "Arr Rolling 60MIN Count", but got the same exact result.

Thank you for your time.

Jay

[Jay_Wang]

Hi all,

I'd like to provide more context to the problem I posted. I have a data file called "Arrival", where in the "Arrived" column time stamp of an event are recorded in the hh:mm format:

Arrived	5Min_Bin
6:15	75
6:18	75
6:20	76
6:24	76
6:26	77
6:28	77
6:37	79
6:42	80
6:43	80
6:47	81
6:51	82
6:53	82
6:55	83
6:57	83
7:00	84
7:02	84
7:06	85
7:08	85
7:09	85
7:12	86
7:33	90
7:42	92
7:43	92
7:46	93

In a dim tablecalled Time5, I have a 5MIN_Bin column which is from 0 to 287 (for the 24 hours, or 1440 minutes), what I want to do is sum the number of "Arrived" in a rolling 60-minute period, i.e. count in the bins from 11 to 0, then 12 to 1 ... I used a measure like this: 

Rolling 60MIN Count =

var this5MIN = Max(Time5[5MIN_Bin])

return

CALCULATE(count(Arrivals[5Min_Bin]), FILTER(ALL(Time5[5Min_Bin]), Time5[5Min_Bin]<=this5MIN && Time5[5Min_Bin]>this5MIN-12))

I plotted this "Rolling 60MIN Count" against the "5MIN_Bin" in a line chart and got something like this:

 while I am expecting ut more like this:

I'd appreciate if someone can look into this and let me know what I did wrong.

Regards,

Jay

[lbendlin]

Your first output uses the wrong chart type.  A line chart implies a (temporal) continuity between items.  Your bucket have no such relationship with each other, so a line chart is misleading.

How do you plan to handle the rolling window across the day boundaries? Do you plan to let it die at midnight and start over?

Your sample data barely covers two hours so showing a 60 minute sliding window is risky.

and yes, I used a line chart. Not accurate.

[Jay_Wang]

Hi Ibendlin,

Thanks so much for the help, I have a lot more data in the table, I shorten it to save space in the post.

Your solution seems to be working, except when I changed the chart to a scatter plot (yes I also used line chart to show the continuous line, no way to do that in the scatter chart in Power Bi), another problem popped up--not every point is plotted. When I use "show as table", you can see that 78, 87, 88, 89, 91, ... are not plotted. I guess the reason is in my "Arrivals" table the 5MIN_Bin doesn't have these fields. what should I do if I want every single 5MIN_Bin, from 0 to 287? In the case of a missing one it should be the same as the previous sliding value.

Thanks again and hope to hear back soon.

Regards,

Jay

[lbendlin]

You linked to the actual report. Please provide a download link for the pbix.