cancel
Showing results for
Did you mean:
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

## 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])))`
18 REPLIES 18
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])`
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

## Re: Grouping records per hour

hi, @uthall

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

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:

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

## 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.

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

## 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.

Helper II

## Re: Grouping records per hour

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

## Re: Grouping records per hour

Hi @uthall

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

as Below

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.

Helper II

## Re: Grouping records per hour

Thanks,

Did that and got

Announcements

#### 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!

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

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 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

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

Top Solution Authors
Top Kudoed Authors