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

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"
 
  • v-lid-msft's avatar
    v-lid-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    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's avatar
      Clinical_Epi
      Icon for Advocate III rankAdvocate III
      5 years ago

      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

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

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