Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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:
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
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.
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
You linked to the actual report. Please provide a download link for the pbix.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
13 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |