The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now
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?
Solved! Go to 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
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
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
User | Count |
---|---|
158 | |
109 | |
96 | |
83 | |
75 |
User | Count |
---|---|
154 | |
137 | |
131 | |
81 | |
62 |