Counting Events in Progress by Date/Time Hourly Intervals

[Chris_1996]

I have trawled through multiple posts, forums, help pages and am yet to be able to replicate the solutions offered.

The main reason is that most of the solutions count events in progress into timeslots at a daily level, however I want to look at my data at a more granular hourly level. Then derive some other information such as sums/counts/averages/max/mins etc.. 

To start though being able to count events that fall within hourly intervals based on arrival / departure times.

A sample table has been provided below

Essentially if I have an ID with arrival time 01/01/2020 9:20PM and Departure Time 02/01/2020 4:10AM

I want the count to show the inventory as 

01/01/20 8:00PM - 01/01/20 9PM : 0

01/01/20 9:00PM - 01/01/20 10PM : 1

01/01/20 10:00PM - 01/01/20 11PM : 1

01/01/20 11:00PM - 02/01/20 12AM : 1

02/01/20 12:00AM - 02/01/20 1AM : 1

02/01/20 1:00AM - 02/01/20 2AM : 1

02/01/20 2:00AM - 02/01/20 3AM : 1

02/01/20 3:00AM - 02/01/20 4AM : 1

02/01/20 4:00AM - 02/01/20 5AM : 1

02/01/20 5:00AM - 02/01/20 6AM : 0

As more inventory ID data is added - the counts within certain time periods would change to reflect the data..

I have 1500 ID's spanning over 1.5Years so at hourly time intervals becomes a reasonably large data set (13K rows of hourly timeslots as above)

ID	Weight	Height	Length	Depth	Store	Arrival Date	Arrival Time	Arrival Date/Time	Departure Date	Departure Time	Departure Date/Time
1	822.0	63.0	92	32.0	Online	30/06/2018	5:46:00	30/06/2018 05:46:00	1/07/2018	7:58:00	01/07/2018 07:58:00
2	877.0	55.0	54	38.0	Retail	30/06/2018	0:50:00	30/06/2018 00:50:00	1/07/2018	13:58:00	01/07/2018 13:58:00
3	267.0	87.0	70	39.0	Retail	30/06/2018	14:44:00	30/06/2018 14:44:00	1/07/2018	19:00:00	01/07/2018 19:00:00
4	509.0	67.0	52	14.0	Retail	30/06/2018	20:52:00	30/06/2018 20:52:00	1/07/2018	21:23:00	01/07/2018 21:23:00
5	537.0	97.0	57	14.0	Online	1/07/2018	10:32:00	01/07/2018 10:32:00	2/07/2018	4:36:00	02/07/2018 04:36:00
6	602.0	98.0	75	35.0	Online	1/07/2018	23:33:00	01/07/2018 23:33:00	3/07/2018	4:08:00	03/07/2018 04:08:00
7	450.0	51.0	66	36.0	Online	1/07/2018	20:40:00	01/07/2018 20:40:00	3/07/2018	8:17:00	03/07/2018 08:17:00
8	382.0	71.0	60	35.0	Retail	1/07/2018	21:23:00	01/07/2018 21:23:00	3/07/2018	12:00:00	03/07/2018 12:00:00
9	850.0	79.0	67	16.0	Online	2/07/2018	15:36:00	02/07/2018 15:36:00	4/07/2018	2:26:00	04/07/2018 02:26:00
10	779.0	92.0	61	36.0	Retail	3/07/2018	6:25:00	03/07/2018 06:25:00	3/07/2018	20:56:00	03/07/2018 20:56:00
11	287.0	67.0	67	11.0	Retail	3/07/2018	10:20:00	03/07/2018 10:20:00	4/07/2018	16:45:00	04/07/2018 16:45:00
12	149.0	67.0	58	19.0	Online	4/07/2018	5:40:00	04/07/2018 05:40:00	5/07/2018	5:13:00	05/07/2018 05:13:00
13	783.0	73.0	69	17.0	Retail	4/07/2018	1:20:00	04/07/2018 01:20:00	5/07/2018	11:42:00	05/07/2018 11:42:00
14	30.0	52.0	95	18.0	Online	5/07/2018	10:30:00	05/07/2018 10:30:00	6/07/2018	12:04:00	06/07/2018 12:04:00
15	442.0	66.0	81	11.0	Retail	4/07/2018	10:25:00	04/07/2018 10:25:00	6/07/2018	15:17:00	06/07/2018 15:17:00

[Greg_Deckler]

Either this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/td-p/409364

Or possibly this:

https://community.powerbi.com/t5/Quick-Measures-Gallery/Hour-Breakdown/m-p/625085#M306

[Chris_1996 · Accepted solution]

@Greg_DecklerSo the open tickets link that you sent through is working so far. I have been able to create a new table with the row by row line items by ID & DATE/TIME. 

One issue that you might be able to quickly help me with.

Using the Table formula in the PBI file (below). It is currently not aligning the data as I wish.

For instance: I have an ID that has a start time at 12/07/2018 7:50am. I want the new table to create a row item for that ID between

TIME SLOT START: 12/07/2018 7:00am

TIMESLOT END: 12/07/2018 8:00AM

At the moment it is not associating that time within that time interval and instead, the first timestamp for that ID is the 8am-9am group.

Any thoughts?

"Table =

VAR tmpTickets = ADDCOLUMNS('Tickets',"Effective Date",IF(ISBLANK([Closed Date]),TODAY(),[Closed Date]))

VAR tmpTable =

SELECTCOLUMNS(

    FILTER(

        GENERATE(

            tmpTickets,

            'Calendar'

        ),

        [Date] >= [Opened Date] &&

        [Date] <= [Effective Date]

    ),

    "ID",[Ticket Num],

    "Date",[Date]

)

RETURN tmpTable"

[Greg_Deckler]

@Chris_1996  - Any chance you can send me the PBIX that you have or share it as a link? Would be easier than me trying to recreate what you have. I'm not seeing where you have the time in your table.

[Chris_1996]

Hi @Greg_Deckler ,

Unfortunately can't figure out how to upload the files themselves but have taken photos which should help.

4 Images:

1. Sample Data (date/time columns are arrival date time and departure date/time

2.  TimeSlot Data Table - has hour intervals for all date/times found in the sample data set

3. New Table- uses the formula as suggested in the open tickets forum (can be seen in image)

4. Resulting table for ID 1.

As seen in image 4. Columns "Arrival", "Departure" is derived from the sample data itself. and "TimeSlot Start" and "TimeSlot End" is derived from the timeslot table. 

For ID 1: Arrival is 5:46 AM on the 30/06. I want it to appear as a count in the timeslot 5AM-6AM. However it is starting at the 6AM-7AM timeslot.

[Chris_1996]

@Greg_Deckler,

As a temporary solution I was able to create a dummy column to meet my needs

In my Sample Data table I created an arrival dummy that was = arrival date/time - time(1,0,0).

Then used this column as timeslot start date >= dummy rather than timeslot start date > arrival date/time so that it would include that row in the hour timeslot i was after..

[Chris_1996]

@Greg_Deckler,

I may have just actually found a fault in the solution.

When the timeslot has 0 events registered, the table creation does not create a row for this.

Therefore if you were wanitng to analyse occasions when there are 0 events this does not allow for this..

[v-lid-msft]

Hi @Chris_1996 ,

We can try to create a calculated table as an axis and use a measure to meet your requirement:

Calculated Table (the AxisValue Column is sort by the SortColumn) 

Axis Table =
ADDCOLUMNS (
    ADDCOLUMNS (
        CROSSJOIN (
            SELECTCOLUMNS (
                DISTINCT (
                    UNION (
                        DISTINCT ( 'Table'[Arrival Date] ),
                        DISTINCT ( 'Table'[Departure Date] )
                    )
                ),
                "Date", [Arrival Date]
            ),
            FILTER (
                CROSSJOIN (
                    SELECTCOLUMNS ( GENERATESERIES ( 0, 23, 1 ), "StartHour", [Value] ),
                    SELECTCOLUMNS ( GENERATESERIES ( 1, 24, 1 ), "EndHour", [Value] )
                ),
                [StartHour] = [EndHour] - 1
            )
        ),
        "AxisValue", FORMAT ( [Date], "DD/MM/YY" ) & " "
            & IF (
                [StartHour] = 0,
                12,
                IF ( [StartHour] > 12, [StartHour] - 12, [StartHour] )
            ) & ":00"
            & IF ( [StartHour] > 12, "PM", "AM" ) & " - "
            & FORMAT ( [Date] + IF ( [EndHour] = 24, 1, 0 ), "DD/MM/YY" ) & " "
            & IF ( [EndHour] > 12, [EndHour] - 12, [EndHour] ) & ":00"
            & IF ( [EndHour] = 24, "AM", IF ( [EndHour] > 12, "PM", "AM" ) )
    ),
    "SortColumn", INT ( [Date] ) * 24 + [StartHour]
)

Measure: 

Value = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[ID] ),
    FILTER (
        'Table',
        NOT (
            'Table'[Arrival Date/Time]
                > ( MAX ( 'Axis Table'[Date] ) + Time(MAX ( 'Axis Table'[EndHour] ),0,0) )
                || 'Table'[Departure Date/Time]
                    < ( MIN ( 'Axis Table'[Date] ) + Time(MIN ( 'Axis Table'[StartHour] ),0,0) )
        )
    )
)

If it doesn't meet your requirement, Could you please show the exact expected result based on the Tables that you have shared？

Best regards,

[Clinical_Epi]

Hello Greg

The solution that you provided Chris is excellent and exactly what I am looking for, Great Job. Unfortunately I have found a peculiar glitch with it which I have been unable to resolve. I’m hoping you can take a look at it

For the 30th,1st and 4th, 11pm -12 is not appearing. The values for these times seem to be counted in a later date.

The Axis value shows all date time periods. However when the value measure is added, 11pm -12 disappears for the dates mentioned

I have tried all sorts of things. I’m wondering if 12 midnight isn’t recognised by the values measure as being after 11PM?

This will be a great solution if you can get it fully working. I intend to use it to show hospital occupancy by the hour. Good Luck with appreciate Roy