Forum Discussion
Counting Events Per Hour Per day
Hi,
So i am trying to calucated the the numbers of truckers we service per hour. Trucks could be here for multiple hours and they need to be counted as if they waiting in a queue. For a single day outlook a solutation creating a temp table worked, but I need to look at mutliple days and i do not know how to add this functionality.
I was looking at something like this: https://community.powerbi.com/t5/Desktop/Counting-Events-in-Progress-by-Date-Time-Hourly-Intervals/td-p/969848
This is the solution i implemented for a single day: https://community.powerbi.com/t5/Desktop/Counting-numbers-per-hour-between-a-start-and-end-time/td-p/591494
It does work but when i bring in certain citeria such as service lane the forumula breaks.
I want to have a day fullment % and then be able to right click and drill through to that specific day and then have the break down by stack per hour like the bi screenshots.
2 Replies
Hi, Anonymous
As a general advise, please, provide:
1. Sample (dummy dataset) data as text
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.It will increase your chances to get an answer as no one wants to retype your data from an image.
No need to show real data, you can always create some short dummy dataset to illustrate your issue.1) Uploading files
Especially when you have a problem with a DAX statement or the data model, it would be best to provide a pbix-file containing the sample data. You might have seen posts here in the forum where files are directly attached and have searched for the button to do exatly that. But unless you're a Microsoft employee or a super user this feature will not be available for you. Instead you have to upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post.
Link to Dummy Data: https://docs.google.com/spreadsheets/d/1ad6eDm-yHuyPI6kqJrowMxhy6qN-jMHt/edit?usp=sharing&ouid=116945492848402880459&rtpof=true&sd=true
For each day per hour i want to know how many CTR's were completed.
For example, at 9/22, 10 CTR's started at 6 am. At the end of 6am, we only completed 4 CTR. We have a 40% completion rate (4/10).
"Change Date" colomn would be considered the completed date. I want to apply this calculation for all hours we operated for a specific day (some days we work 8 hours and hours we work 12 etc.) The hours operation can be found by knowing the max time "Change date" for a given day.
In my original post, I found another thread which helped me figure it out for a single day, but i need this for multiple days. Also when I drill through to a specific day, I want to see this % logic being applied to each of the unique CTR_TICKET_POS_ID and see there hourly breakdown.
