cancel
Showing results for
Search instead for
Did you mean:
Highlighted
kristen Regular Visitor

## 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              Closed1234         2017/01/01 03:01    2017/01/03 16:441235         2017/01/03 12:31    2017/01/25 14:211236         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?

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor

## Re: Calculating Open Tickets at Specific Hour

Hi @kristen,

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

2 REPLIES 2
LMeijdam Established Member

## Re: Calculating Open Tickets at Specific Hour

Hi @kristen,

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 Super Contributor

## Re: Calculating Open Tickets at Specific Hour

Hi @kristen,

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