Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Calculating Open Tickets at Specific Hour

Would appreciate the community's help on this one!

 

I have a bunch of support ticket data:

 

Ticket #     Opened              Closed
1234         2017/01/01 03:01    2017/01/03 16:44
1235         2017/01/03 12:31    2017/01/25 14:21
1236         2017/02/03 23:42    2017/03/01 13:01

I've unpivoted and added a conditional column:

Ticket #    Date                Attribute    Count
1234         2017/01/01  03:01   Open         1 
1234         2017/01/03  16:44   Closed      -1
1235         2017/01/03  12:31   Open         1
1235         2017/01/25  14:21   Closed      -1
1236         2017/02/03  23:42   Open         1
1236         2017/03/01  13:01   Closed      -1

I used a measure to calculate a running/cumulative total and it's working great! I can easily see the open tickets by day:

Open Tickets = SUMX(FILTER(ALL(Tickets),Tickets[Date]<=MAX(Tickets[Date])),[Count])

HOWEVER, I've been asked to calculate the # of open tickets at our daily start-up (08:00). My question is this:

 

How can I calculate a "time stamp" of our open tickets at 08:00, rather than a sum for the whole day? 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Anonymous,



    HOWEVER, I've been asked to calculate the # of open tickets at our daily start-up (08:00). My question is this:

     

    How can I calculate a "time stamp" of our open tickets at 08:00, rather than a sum for the whole day? 

    I'm not sure if I understand your requirement correctly. Do you mean calculating the sum of open tickets before your daily start-up (08:00), rather than the whole day?

     

    If that is the case, you can firstly create a individual calendar table if you don't it yet.

     

    Date = CALENDARAUTO()

     

     

    Then you should be able to use the formula below to create a new measure to calculate the sum of open tickets before your daily start-up (08:00).

     

    Open Tickets2 =
SUMX (
    FILTER ( ALL ( Tickets ), Tickets[Date] <= MAX ( 'Date'[Date] ) + 8 / 24 ),
    [Count]
)

     

    And then you can show the measure with the Date column from the Date table on the report. :smileyhappy:

     

    Regards

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Anonymous,

     

    Can't you make a time slicer ? one that has column "Date" as value, you would be able to manually select each day and hour of choice.

     

    Let me know if this worked/ was useful.

     

    Regards,

    L.Meijdam

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    8 years ago

    Hi Anonymous,



    HOWEVER, I've been asked to calculate the # of open tickets at our daily start-up (08:00). My question is this:

     

    How can I calculate a "time stamp" of our open tickets at 08:00, rather than a sum for the whole day? 

    I'm not sure if I understand your requirement correctly. Do you mean calculating the sum of open tickets before your daily start-up (08:00), rather than the whole day?

     

    If that is the case, you can firstly create a individual calendar table if you don't it yet.

     

    Date = CALENDARAUTO()

     

     

    Then you should be able to use the formula below to create a new measure to calculate the sum of open tickets before your daily start-up (08:00).

     

    Open Tickets2 =
SUMX (
    FILTER ( ALL ( Tickets ), Tickets[Date] <= MAX ( 'Date'[Date] ) + 8 / 24 ),
    [Count]
)

     

    And then you can show the measure with the Date column from the Date table on the report. :smileyhappy:

     

    Regards