How to get hourly aggregate counts and show on a Weekly Calendar/Table Visual?

[iamriz]

Hello, I want to get the hourly aggregate counts and show them on a weekly Calendar/Table visual same as below screenshot.

Sample Week Calendar

I would appreciate if anyone can help give tips on how to best achieve this.

Thank you.

[amitchandak · Accepted solution]

@iamriz ,

You need split date into two part date and time

Date = [date time].date

join with date calendar having week info

Time = [date time].time

and join with time calendar having hour bucket

Time calendar :https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

week calendar : https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

WeekDay Name = FORMAT([Date],"ddd")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[vanessafvg · Accepted solution]

is this what you looking for? see attached

[v-yingjl · Accepted solution]

Hi @iamriz ,

Create two calculated columns and one measure can achieve this:

Calculated columns:

Time = TIME(HOUR('Sample_data'[Datetime]),0,0)

Day = FORMAT(DAY('Sample_data'[Datetime]),"dddd")

Measure:

Result = COUNTROWS('Sample_data')

Use a Matrix to show the result:

Attached sample file that you can refer: How to get hourly aggregate counts.pbix

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[vanessafvg · Accepted solution]

is this what you looking for? see attached

[harshnathani]

Hi @iamriz ,

Se if these solutions help you.

https://community.powerbi.com/t5/Desktop/Date-Hierarchy-at-Hourly-Level/m-p/210538

https://community.powerbi.com/t5/Desktop/Aggregate-hourly-bytes-to-daily/m-p/732086

https://community.powerbi.com/t5/Desktop/Aggregate-Hourly-graph/m-p/890081

Regards,

Harsh Nathani

[amitchandak · Accepted solution]

@iamriz ,

You need split date into two part date and time

Date = [date time].date

join with date calendar having week info

Time = [date time].time

and join with time calendar having hour bucket

Time calendar :https://kohera.be/blog/power-bi/how-to-create-a-time-table-in-power-bi-in-a-few-simple-steps/

week calendar : https://community.powerbi.com/t5/Community-Blog/Any-Weekday-Week-Decoding-Date-and-Calendar-2-5-Powe...

WeekDay Name = FORMAT([Date],"ddd")

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[vanessafvg]

can you share some of your data in text form?

[iamriz]

@vanessafvg, here is the sample data: https://drive.google.com/file/d/1ljK-ZPr4eYX3Yk87SPKmRrvhwXJKUetd/view?usp=sharing