Skip to main content
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 

Get Fabric Certified for FREE during Fabric Data Days. Don't miss your chance! Request now

Reply
amit_maniyar
Helper I
Helper I

Hourly data - Rolling Count each hour, Max Count each hour , Min Count each hour using DAX or Query

‎03-02-2023 07:45 AM

Hello All,

I have a table with datetime fields (Minute level data)

RecordUniqueID (Text) , AdmissionDatetime (Datetime) , DischargeDatetime (Datetime)

I have a DimDate (date, month, year etc ) and DimTime ( hour, minute level columns) table as well in the data model.

I am in need of these measures to plot on hourly graph. I have attached few sample data as well

 

Rolling last 24 hours Average count
Max Count in hourly chart - out of all the records in last x days
Min count in hourly chart - out of all the records in last x days

 

Appreciate any help! thanks

 

RecorduniqueIDAdmitDateTimeDischargeDatetime
C0CDA431-0DCF-477E-8DEF-481A6516ACD92/24/2023 23:373/2/2023 10:12
02BFCD06-B337-4D0C-B0EA-F3B1A6BF3AFE3/2/2023 2:113/2/2023 10:11
2D3CE549-AD22-48C7-B925-7D63E6748E323/1/2023 5:583/2/2023 10:10
27572AF3-3C87-491C-9806-83217A72310B3/1/2023 7:243/2/2023 9:50
553B1A86-A6B2-4CA6-9C0F-FFAAEBA82A2912/6/2022 7:253/2/2023 9:34
81C6FDFF-37AD-4F43-8FAD-28347A3C22C92/27/2023 22:063/2/2023 9:21
FE762963-EA48-454C-BFC7-E24C4B2C1E8C3/1/2023 8:363/2/2023 9:15
33CFFEA9-7D52-417F-BD12-E9A72DADA61A2/19/2023 21:573/2/2023 9:11
EDCE4570-9349-41E1-BDFC-0BA9C5A9E3A63/1/2023 19:423/2/2023 8:06
07B0E89D-AC1E-49BE-958A-E26A4D459FF93/1/2023 10:223/2/2023 8:06
9195EFEB-1883-4C6F-87F6-38E84C17ADD92/9/2023 19:393/2/2023 4:26
2F3CDBE4-C0FE-44F5-AB69-652DC87EEF452/21/2023 14:333/1/2023 23:35
7E1B48B1-C878-4510-80AA-04381E583A662/28/2023 21:233/1/2023 22:38
F76EBB11-27D9-4225-A53E-31C85C3D60532/27/2023 15:393/1/2023 22:36
421D99A0-3C50-4AE7-A8EB-27788F8F728B2/28/2023 13:443/1/2023 21:08
BEA9AB77-EE36-407A-B4A6-A7DDB6FAFFA02/23/2023 22:133/1/2023 21:07
1C4E5F38-4265-4C0A-8A4F-B64FFBFAD9533/1/2023 6:053/1/2023 18:47
DB4298D6-97E5-48DD-ABAB-9A0EAC6694BA2/27/2023 13:583/1/2023 18:22
0323D124-0BA6-44BB-B61A-CE7FF99128222/26/2023 5:243/1/2023 18:14
3FDB7318-FD97-4D95-9FAB-86A2D65CCE762/25/2023 22:213/1/2023 18:00
6A8A27AF-8F49-459C-86BF-5C4ECF1D32D31/31/2023 20:453/1/2023 17:59
C9C03204-6A1B-46C0-A316-39122E55CFC02/28/2023 10:113/1/2023 17:43
15EC5084-EA15-4CF3-B921-2E7E233F233B2/26/2023 22:023/1/2023 17:09
6C3A591F-3A8E-4A60-9A9C-485D9537C52E2/28/2023 19:133/1/2023 16:51
60215710-8857-4F49-B55F-72F35F42F6772/25/2023 22:463/1/2023 16:36
135874C0-B4C4-4A12-99AF-CB897EBE2F872/20/2023 19:483/1/2023 16:35
C24C6A81-FD51-42C2-911A-5AE4431825FC2/23/2023 19:203/1/2023 16:34
45B921AB-0C0A-4396-85D2-927A808316CE2/26/2023 18:253/1/2023 16:23
37D851E8-54D3-4B3D-A0A6-43062A68061F2/24/2023 9:213/1/2023 16:22
5FCDECA8-8CDF-4A3A-8E15-EEA4EBAC9B6E2/26/2023 12:153/1/2023 16:16
07C7FBCC-F431-423D-924A-390655100F4E2/28/2023 22:283/1/2023 16:15
4E0858EE-262C-48F2-B8D6-CF8367E0A51F2/3/2023 17:103/1/2023 15:53
3B15A944-141F-4E88-89BA-81914F713C0B2/9/2023 12:073/1/2023 15:46
E7458447-62F4-4D2F-8C3B-9B204459CD662/28/2023 12:233/1/2023 15:41
884570FF-3A2B-47DC-9DF0-61417E4E76AC2/28/2023 10:033/1/2023 15:41
DFF3EBE4-BEDF-42A9-AB3B-15D272662E9A2/10/2023 21:013/1/2023 15:32
60F3A7C5-7B33-431D-8EB9-E7441FB0E2082/27/2023 15:183/1/2023 15:31
29CA755B-2A54-4FC7-A731-05C45EDBCBD02/24/2023 11:463/1/2023 15:22
B9B73DFD-23C0-4861-B95B-D5308B6D91853/1/2023 9:083/1/2023 15:18
0DCBD37F-5839-4E2E-8719-2314E1E0DE102/17/2023 14:563/1/2023 15:10
02CE34DB-5E7A-4E5A-9B9F-4BAB03842A692/21/2023 5:153/1/2023 14:53
A45A13E6-300A-4513-B469-A65CE50B586D1/11/2023 19:143/1/2023 14:21
B4641AD9-82E8-47DD-AEAB-B789878C92F12/23/2023 18:513/1/2023 14:14
769DFC14-9540-43D4-A19B-C5B0CB315D132/26/2023 11:303/1/2023 14:07
0A632A1E-5041-4DE9-AF51-DBF08577FABF2/16/2023 22:553/1/2023 13:59
6088F6CC-A9CF-4D66-BDF5-E9E5CF162BC42/27/2023 6:423/1/2023 13:59
762E80DF-EA71-4390-9166-2A43D44E1B922/19/2023 2:103/1/2023 13:51
45DF5327-FEE4-487B-BFA9-DA16F451050D2/7/2023 18:593/1/2023 13:35
BBFE2405-8B47-4830-8A86-2F318D34BAA82/11/2023 22:093/1/2023 13:33
AFC47976-34C2-45F5-989A-6D2BE84CC7A62/28/2023 8:273/1/2023 13:22
256427A4-731B-4023-A31A-C556332FCFA12/28/2023 6:233/1/2023 13:22
3FD83991-E038-4BF8-BC9E-11E3D5F689892/27/2023 13:513/1/2023 13:20
2F48BF01-7597-4FDF-84C1-F07EFD7C08342/21/2023 15:293/1/2023 13:15
5642BC64-68C3-4E52-8F28-A6C84962A2B52/27/2023 3:373/1/2023 13:07
BC335CC5-8054-45AB-932C-22883F58419D2/27/2023 22:353/1/2023 12:58
716C08AB-43F3-45DD-949F-3C7759FCCC5D2/27/2023 13:453/1/2023 12:58
49DF511C-AB35-4C31-9541-CCCEF4A1872C2/28/2023 12:543/1/2023 12:49
0C09F341-E490-4EA3-8A12-CEFD711D38A92/28/2023 6:373/1/2023 12:49
A6E86105-3E34-4A60-8D8F-F2AA4D06A2782/27/2023 6:033/1/2023 12:46
98C3C850-82BE-4C74-B787-9FB4147249A22/26/2023 18:513/1/2023 12:45
10EBB585-E38C-4C9D-92F2-32193740F20D2/26/2023 15:003/1/2023 12:45
9FE195E8-8589-4503-A896-5CC1DFAA92732/28/2023 3:523/1/2023 12:39
46219775-384E-469D-9B8C-96C184330A0F2/27/2023 23:023/1/2023 12:38
79822A7E-875D-46B9-ADF8-A201B95940762/27/2023 10:113/1/2023 12:27
D85BAD5A-06E2-4DD2-8EB7-0A52284EDC9A2/27/2023 8:253/1/2023 12:27
D1022D56-5267-4F12-95AB-B3ED303993C92/24/2023 2:563/1/2023 12:26
2E0E7117-157F-49C9-B3D5-A06E6CA6047B2/15/2023 23:503/1/2023 12:08
54BBD805-9751-44F8-BC14-A518CDC95BCC2/28/2023 6:413/1/2023 12:05
774ECE36-ACD1-4D42-B3ED-79894EE2008E2/27/2023 13:223/1/2023 12:04
3B324885-E426-44AA-9B61-59B51BBD49F62/22/2023 6:063/1/2023 11:57
93AC6FFF-2530-43A4-B6A8-D89CAE0D950A2/14/2023 15:233/1/2023 11:52
CA59D638-80FE-4BB0-A203-86AD8D76ED471/23/2023 16:103/1/2023 11:45
ADECD81E-38C1-4006-9FD4-F9492050005C2/21/2023 15:523/1/2023 11:44
A76CD0B7-4F08-42BF-AB81-010676769C432/22/2023 7:513/1/2023 11:33
56B251F4-204A-4C03-8DEE-591E210F59592/28/2023 0:553/1/2023 11:28
ECD9CB2E-C827-42B3-BB2F-B59CC2A397C12/27/2023 7:193/1/2023 11:28
6A9D3937-AEB0-450E-B9CB-B863558D7F312/16/2023 22:203/1/2023 11:13
44C15ED9-10F1-4540-8A78-41C8A26A4FDA2/26/2023 9:493/1/2023 11:10
12C4140E-6B1D-4CF9-A080-A8534FE7FD943/1/2023 2:343/1/2023 10:55
3809CDD9-17E4-4323-804B-3F40179D6B942/28/2023 7:573/1/2023 10:54
ED6FDACD-7262-4580-91EF-7D06851A33942/28/2023 23:263/1/2023 10:40
6A04DF61-11DE-4650-A428-1F437A4AD0172/28/2023 6:063/1/2023 10:34
8D1B58F7-006F-4A78-8874-4E47557917092/21/2023 16:323/1/2023 10:32
8701D582-9781-4225-9582-B0855CAFEB3A1/28/2023 23:173/1/2023 10:24
FA9F3666-1080-4C72-91E6-0A0B3B17848B2/24/2023 20:043/1/2023 10:22
FE54CE27-C174-4594-8106-A1A7290B2BE12/28/2023 17:583/1/2023 10:12
7BCFB53E-9564-44FB-ACA6-441D2601F9552/16/2023 5:423/1/2023 9:59
9ECDE521-59BB-4826-93AA-A35B6C48A8542/13/2023 23:203/1/2023 6:00
49FA7F3E-C588-4624-9DE3-A6F4EDDBC47E2/28/2023 16:533/1/2023 4:30
02CBDB26-2DE6-449D-892F-8806E87CEC872/27/2023 23:313/1/2023 1:51
AAF2A21D-F138-4E45-BFE3-6CE8968F62EB2/27/2023 16:463/1/2023 1:51
EB2FAB4B-AF9F-4C8F-91D3-48C5F27561172/27/2023 21:513/1/2023 0:34
0072E741-DFA6-45B7-AC24-C92D3F54A7582/27/2023 12:183/1/2023 0:34
70721DBA-E85F-4CBA-9EA2-04F9EAADD5EB2/28/2023 9:493/1/2023 0:08
20D9C480-9EB1-458B-8275-A2822F325F102/28/2023 3:322/28/2023 23:51
AC48CAF5-A222-4928-82E1-38140A205FC22/28/2023 1:472/28/2023 22:38
E81FC14C-9953-42C5-B5C8-1B6A07AF1D121/31/2023 10:412/28/2023 22:03
9359EA76-1A46-4D48-8E0B-2E11D25249442/26/2023 15:142/28/2023 22:00
DC348AF1-EA71-4222-A0A2-1A4BB624CB9112/22/2022 12:222/28/2023 20:45
A173DF9D-1A7E-45CC-A44F-B7AC7AE954032/13/2023 10:402/28/2023 20:21

 

 

 

Labels:
Message 1 of 9
1,432 Views
0
1 ACCEPTED SOLUTION
lbendlin
Super User
Super User
In response to amit_maniyar

‎03-17-2023 05:04 AM

Ah, so more like this 

lbendlin_0-1679054362237.png

Your sample data is a little too uniform for meaningful insights

 

lbendlin_1-1679054621450.png

 

View solution in original post

event counter.pbix
Message 8 of 9
1,280 Views
8 REPLIES 8
amit_maniyar
Helper I
Helper I

‎03-15-2023 10:27 PM

Sorry for the late response.

I am trying to count events per hour. also, average of counts per hour.

I hope that makes sense

thanks,

Amit

Message 5 of 9
1,319 Views
0
lbendlin
Super User
Super User
In response to amit_maniyar

‎03-16-2023 05:00 AM

something like this?

lbendlin_0-1678967967425.png

 

what do you mean by "average counts per hour?

 

see attached.

 

event counter.pbix
Message 6 of 9
1,308 Views
0
amit_maniyar
Helper I
Helper I
In response to lbendlin

‎03-17-2023 12:55 AM

For an example 

jan 1, 10 am has 12 event counts 

jan 2, 10 am has 20 event counts

 

I would like to get the average at 10 am (nothing to do with date here) ,

my avg at 10 am will be (12+20) / 2 = 16.

Message 7 of 9
1,295 Views
0
lbendlin
Super User
Super User
In response to amit_maniyar

‎03-17-2023 05:04 AM

Ah, so more like this 

lbendlin_0-1679054362237.png

Your sample data is a little too uniform for meaningful insights

 

lbendlin_1-1679054621450.png

 

event counter.pbix
Message 8 of 9
1,281 Views
amit_maniyar
Helper I
Helper I
In response to lbendlin

‎03-17-2023 07:49 AM

thanks. 

Message 9 of 9
1,269 Views
0
amit_maniyar
Helper I
Helper I

‎03-08-2023 09:08 AM

Yes, I am trying to get the rolling 24 hours count. 

I am also intrested to know min and max count in a hour - which means there will be different counts each hour each day. I want to find the max count at for ex. 11 am - out of all the days we have. 

 

Sorry I am not very good at explaining. any help is appreciated.

Thanks

Message 3 of 9
1,385 Views
0
lbendlin
Super User
Super User
In response to amit_maniyar

‎03-08-2023 09:48 AM

counts of what?  You have two separate datetime columns. Please be more specific.

Message 4 of 9
1,378 Views
0
lbendlin
Super User
Super User

‎03-04-2023 12:07 PM

What are you measuring?  Number of events per hour?  Average duration?

Message 2 of 9
1,421 Views
0

Helpful resources

Announcements
November Power BI Update Carousel

Power BI Monthly Update - November 2025

Check out the November 2025 Power BI update to learn about new features.

Fabric Data Days Carousel

Fabric Data Days

Advance your Data & AI career with 50 days of live learning, contests, hands-on challenges, study groups & certifications and more!

FabCon Atlanta 2026 carousel

FabCon Atlanta 2026

Join us at FabCon Atlanta, March 16-20, for the ultimate Fabric, Power BI, AI and SQL community-led event. Save $200 with code FABCOMM.

View All
Top Solution Authors
View All
Top Kudoed Authors
View All