Forum Discussion

Chris_1996's avatar
Chris_1996
Icon for Helper I rankHelper I
6 years ago
Solved

Counting Events in Progress by Date/Time Hourly Intervals

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 timesl...
Need Help
Show and Tell
Tutorial Requests
  • Chris_1996's avatar
    Chris_1996
    6 years ago

    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"
     
Chris_1996's avatar
Chris_1996
Icon for Helper I rankHelper I
6 years ago

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's avatar
Chris_1996
Icon for Helper I rankHelper I
6 years ago

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's avatar
    Chris_1996
    Icon for Helper I rankHelper I
    6 years ago

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