cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
kristen Regular Visitor
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              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? 

1 ACCEPTED SOLUTION

Accepted Solutions
v-ljerr-msft Super Contributor
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()

t1.PNG

 

 

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

 

Regards

2 REPLIES 2
LMeijdam Established Member
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
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()

t1.PNG

 

 

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

 

Regards