cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
uthall Helper II
Helper II

Grouping records per hour

Hello,

 

I have a table with records with a start time and stop time

 

I want to sum the total records per hour.

 

For example, record 1 starts at 1300 and stops as 1500, and record 2 starts at 1300 and finshes at 1700, then i want to see hour 13 with 2 records, hour 14 with 2 records, hour 15 with 2 records, hour 16 with 1 record, and hour 17 with 1 record.

 

Is this possible? 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Super User III
Super User III

Re: Grouping records per hour

If you want to show blanks, then on the table options, click the dropdown on the Hour field, and check the option to "Show items with no data"

 

If you want to show a 0 instead of a blank, you can change the previous measure to:

Measure 2 = COUNTAX(FILTER(Table1,[Start]<=SELECTEDVALUE(Table2[Time])&&[Stop]>=SELECTEDVALUE(Table2[Time])),[RecordID])+0

As far as splitting by RecordID vs Customer, you never mentioned that the data was also split into customers.  How are RecordID and customer related?  If you want it to only count Customers, you can replace [RecordID] in the above query with DISTINCT([CustomerID]) instead.

 

Here's how you would get a count of distinct Customers instead of a count of records:

Measure 3 = CALCULATE(DISTINCTCOUNT(Table1[CustomerID])+0,FILTER(Table1,[Start]<=SELECTEDVALUE(Table2[Time])&&[Stop]>=SELECTEDVALUE(Table2[Time])))

View solution in original post

18 REPLIES 18
Super User III
Super User III

Re: Grouping records per hour

I'm assuming you have a table that lists each of the hours already that you can create this measure on.  This formula assumes Table1 has [RecordID], [Start], and [Stop] entries, while Table2 is a list of hours (1300, 1400, 1500, etc) in a DATETIME format.

 

Measure2 = COUNTAX(FILTER(Table,[Start]<=SELECTEDVALUE(Table2[Time])&&[Stop]>=SELECTEDVALUE(Table2[Time])),[RecordID])
uthall Helper II
Helper II

Re: Grouping records per hour

Thanks,

 

No, i dont have a table of hours over a day (24hrs).

 

Is it possible without this, or do i need to create a specific table just for this?

Community Support
Community Support

Re: Grouping records per hour

hi, @uthall 

You need to define a dim table of hours over a day (24hrs) In advance as below:

3.JPG

Then use the measure as above

Measure = COUNTAX(FILTER(Table1,[Start]<=SELECTEDVALUE('Dim time'[Time])&&[Stop]>=SELECTEDVALUE('Dim time'[Time])),[RecordID])

Now drag Time field from dim table and the meausre into visual

Result:

11.JPG

 

and here is pbix file, please try it.

Also here is another similar post for you refer to:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

 

 

Best Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Super User III
Super User III

Re: Grouping records per hour

You need a specific table just for this. PowerBI doesn't have a way to iterate over data that isn't in a table.  There's no while or for loops.

 

Creating the dimension table mentioned above is the only way to  easily accomplish this.   If it helps, you can hide the table from report views once you've created it.

uthall Helper II
Helper II

Re: Grouping records per hour

Thanks Lin and Cmcmahan 

 

Is there any way to create the dimension table other than manually?

Super User IV
Super User IV

Re: Grouping records per hour

Hi @uthall 

 

You can use Query editor for that, please see the M code below.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTI0tjIwACIQ0xTKjNWJVjJCkzOHycUCAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [ID = _t, Start = _t, End = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"Start", type time}, {"End", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "TimeRecords", each List.Times( [Start], Duration.Hours( Duration.From( [End] - [Start] ) ) + 1, #duration(0, 1, 0, 0))),
    #"Expanded TimeRecords" = Table.ExpandListColumn(#"Added Custom", "TimeRecords"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded TimeRecords",{{"TimeRecords", type time}})
in
    #"Changed Type2"

Regards,
Mariusz

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

uthall Helper II
Helper II

Re: Grouping records per hour

@Mariusz 

 

Sorry to be a dodo........can you briefly explain how i add the query to a new table in the query editor...

Super User IV
Super User IV

Re: Grouping records per hour

Hi @uthall 

 

In query editor, go to 
Home > New Source > Blank Query 

as Below

image.png

 

Next, go to Home > Advanced Editor / and paste the code provided there.

 

Regards,
Mariusz

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

uthall Helper II
Helper II

Re: Grouping records per hour

Thanks,

 

Did that and got

Capture.JPG

 

Helpful resources

Announcements
New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors