Count of events per hour

[SOTO]

I have a table called INCIDENT where I want to be able to show how many INCIDENT_NUMBER are created each hour.

In the table there is a CREATION_DATE Colum in the format of DD/MM/YY HH:MM:SS

Any help please as I am new to DAX

[Greg_Deckler]

@SOTO - There is an HOUR function in DAX. Also, you may find Open Tickets helpful. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147

[Anonymous]

Hi @SOTO 

Follow these steps

1. Change format of CreationDate column to  24 hour format [2001-03-14 13:30:55 (yyyy-mm-dd hh:nn:ss)].

2. Add a new calculated column SOH in your table(Incident) as below.

SOH = HOUR(Incident[CreationDate])

3. Add another calculated column in your table(Incident) as below.

No of Incidents/Hour =
VAR D = Incident[CreationDate].[Date]
VAR StartOfHour = Incident[SOH]
VAR Filtered =
    FILTER (
        ALL ( Incident ),
        Incident[CreationDate].[Date] = D
            && Incident[SOH] = StartOfHour
    )
RETURN
    CALCULATE ( DISTINCTCOUNT ( Incident[Incident Number] ), Filtered )

This should be enough.

Hope this helps.

Appreciate with a kudos.

Please mark as a solution if this resolves your problem.

Thanks

[TomMartens]

Hey @SOTO ,

make sure that your timestamp column is of type date/time, if it's not you can easily change the data type using PowerQuery like so:

You can also use Power Query to create a column that only contains the hour part of the timestamp like so:

Then leave Power Query using Close & Apply from the home menu.

Create a measure like so:

no of rows = COUNTROWS('Table') 

Adjust the table name 'Table' accordingly.

Change the Aggregation of the Hour column to don't summarize:

Create a new table visual, drag the hour column and the to the table - done:

hopefully, this gets you started.

regards

tom

[mahoneypat · Accepted solution]

Here is a good way to do that

1. In the query editor, split your datetime column into date and time columns.  Select that column and click on the Split Column button, and choose Space as the delimiter.

2. Convert the columns to Date and Time type, respectively

3. Select the Time column and, on the Add Column tab, pull down on Time and choose Hour.  This will add a column with just the Hour.

4.  Make a Table or Matrix visual using the Hour field, and a simple count or distinct count of another field in that table

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat