Calculating Open Tickets at Specific Hour

[Anonymous]

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? 

[v-ljerr-msft · Accepted solution]

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. 

Regards

[Anonymous]

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