Aggregate hourly bytes to daily

[Anonymous]

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

[MFelix · Accepted solution]

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