Forum Discussion
Aggregate hourly bytes to daily
I have data stream that collects data usage per user. The data is in hourly and have been collecting this data regulary. How to I go about so that I can display the aggregate data per day instead of hourly without creating new column since we also need the same data for the line chart.
| time_stamp | User | Bytes |
| 6/5/2019 0:00 | Steve | 374880968.8 |
| 6/5/2019 1:00 | Steve | 332580500 |
| 6/5/2019 2:00 | Steve | 302910031.3 |
| 6/5/2019 3:00 | Steve | 346099281.3 |
| 6/5/2019 4:00 | Steve | 315223437.5 |
| 6/5/2019 5:00 | Steve | 339442937.5 |
| 6/5/2019 6:00 | Steve | 333164312.5 |
| 6/5/2019 7:00 | Steve | 391347125 |
| 6/5/2019 8:00 | Steve | 432070781.3 |
| 6/5/2019 9:00 | Steve | 476643562.5 |
| 6/5/2019 10:00 | Steve | 434617062.5 |
| 6/5/2019 11:00 | Steve | 417475968.8 |
| 6/5/2019 12:00 | Steve | 470397437.5 |
| 6/5/2019 13:00 | Steve | 507142406.3 |
| 6/5/2019 14:00 | Steve | 679750750 |
| 6/5/2019 15:00 | Steve | 528020031.3 |
| 6/5/2019 16:00 | Steve | 426049312.5 |
| 6/5/2019 17:00 | Steve | 427328593.8 |
| 6/5/2019 18:00 | Steve | 362404625 |
| 6/5/2019 19:00 | Steve | 329924593.8 |
| 6/5/2019 20:00 | Steve | 362511375 |
| 6/5/2019 21:00 | Steve | 395737125 |
| 6/5/2019 22:00 | Steve | 365116406.3 |
| 6/5/2019 23:00 | Steve | 324718562.5 |
| 6/6/2019 0:00 | Steve | 338591718.8 |
| 6/6/2019 1:00 | Steve | 320378500 |
| 6/6/2019 2:00 | Steve | 296023562.5 |
| 6/6/2019 3:00 | Steve | 367451000 |
| 6/6/2019 4:00 | Steve | 380653093.8 |
| 6/6/2019 5:00 | Steve | 392283656.3 |
| 6/6/2019 6:00 | Steve | 457481281.3 |
| 6/6/2019 7:00 | Steve | 432150937.5 |
| 6/6/2019 8:00 | Steve | 497446781.3 |
| 6/6/2019 9:00 | Steve | 449558437.5 |
| 6/6/2019 10:00 | Steve | 494554062.5 |
| 6/6/2019 11:00 | Steve | 463344406.3 |
| 6/6/2019 12:00 | Steve | 493005031.3 |
| 6/6/2019 13:00 | Steve | 471070468.8 |
| 6/6/2019 14:00 | Steve | 425981750 |
| 6/6/2019 15:00 | Steve | 451298218.8 |
| 6/6/2019 16:00 | Steve | 343354593.8 |
| 6/6/2019 17:00 | Steve | 413200187.5 |
| 6/6/2019 18:00 | Steve | 342956687.5 |
| 6/6/2019 19:00 | Steve | 344050281.3 |
| 6/6/2019 20:00 | Steve | 368797812.5 |
| 6/6/2019 21:00 | Steve | 296131250 |
| 6/6/2019 22:00 | Steve | 327823218.8 |
| 6/6/2019 23:00 | Steve | 275181062.5 |
| 6/7/2019 0:00 | Steve | 330895843.8 |
| 6/7/2019 1:00 | Steve | 285541750 |
| 6/7/2019 2:00 | Steve | 240747750 |
| 6/7/2019 3:00 | Steve | 308361000 |
| 6/7/2019 4:00 | Steve | 324009937.5 |
| 6/7/2019 5:00 | Steve | 341597750 |
| 6/7/2019 6:00 | Steve | 424309218.8 |
| 6/7/2019 7:00 | Steve | 427691875 |
| 6/7/2019 8:00 | Steve | 493539562.5 |
| 6/7/2019 9:00 | Steve | 439180937.5 |
| 6/7/2019 10:00 | Steve | 393510125 |
| 6/7/2019 11:00 | Steve | 402607906.3 |
| 6/7/2019 12:00 | Steve | 519442218.8 |
| 6/7/2019 13:00 | Steve | 494229531.3 |
| 6/7/2019 14:00 | Steve | 498988937.5 |
| 6/7/2019 15:00 | Steve | 502675281.3 |
| 6/7/2019 16:00 | Steve | 501857625 |
| 6/7/2019 17:00 | Steve | 376268562.5 |
| 6/7/2019 18:00 | Steve | 373258781.3 |
| 6/7/2019 19:00 | Steve | 326514281.3 |
| 6/7/2019 20:00 | Steve | 401723875 |
| 6/7/2019 21:00 | Steve | 366760656.3 |
| 6/7/2019 22:00 | Steve | 311002843.8 |
| 6/7/2019 23:00 | Steve | 256467843.8 |
| 6/8/2019 0:00 | Steve | 280781562.5 |
Hi Anonymous ,
On my experience working with dates and times, and looking back at what I have read about dates used in models for Power BI and other BI programs, there are some pieces of information that you need to create on your model to have the full details that you need.
In the case of dates, you need to have Day, month, year for sure depending on the need you have you may also need to have a quarter, week, weekday, month name, etc. Although you may think this some times can be redundant information, in the long run, it will help to give insights to your model when you want to start to drill down or up.
On your question, I believe that you should create a date column, but I go even further and say that maybe you should consider a calendar dimension table to make your time calculations. This is only my opinion.
Looking at your data the possible way to not create new columns is to activate the Auto Date/time option on the options of your PBIX file and use the hierarchy (Year / Month / Day) on your chart/visuals and drill down to the last level and you will get the day level instead of the hour.
On the image to the left you have the hourly data on the right the daily data.
Regards,
MFelix
1 Reply
- MFelix
Super User
Hi Anonymous ,
On my experience working with dates and times, and looking back at what I have read about dates used in models for Power BI and other BI programs, there are some pieces of information that you need to create on your model to have the full details that you need.
In the case of dates, you need to have Day, month, year for sure depending on the need you have you may also need to have a quarter, week, weekday, month name, etc. Although you may think this some times can be redundant information, in the long run, it will help to give insights to your model when you want to start to drill down or up.
On your question, I believe that you should create a date column, but I go even further and say that maybe you should consider a calendar dimension table to make your time calculations. This is only my opinion.
Looking at your data the possible way to not create new columns is to activate the Auto Date/time option on the options of your PBIX file and use the hierarchy (Year / Month / Day) on your chart/visuals and drill down to the last level and you will get the day level instead of the hour.
On the image to the left you have the hourly data on the right the daily data.
Regards,
MFelix