Forum Discussion
Creating Open Tickets / Backlog Evolution Measure & Graph Visual
Hello everyone, I am trying to replicate a Service Now monthly average incident backlog in Power BI, however the measure i have created to count the number of open tickets doesn't return the right amount of tickets.
I have a Date table and an Incidents table with deactivated relationships between Date[Date] and Incidents[opened_at], Incidents[resolved_at].
The Date format is the same in all 3 columns, I have removed the timestamps.
In Service Now the measure created in Analytics is the distinct count of open tickets with a not canceled state :
Opened on Today .or. Opened < 2025-02-12 00:00:00 .and. Resolved is empty .or. Resolved > 2025-02-12 23:59:59 .and. State != Canceled
The measure i have created is below, when i try to show the value in a monthly graph the values shown are wrong, and i do not understand what the problem is. Does anyone have any idea on how to solve this?
Hi,
Can you try this?
Open Incidents =
VAR SelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT(Incidents[number]),
FILTER(
ALL(Incidents),
Incidents[opened_at] <= SelectedDate
&&
(
ISBLANK(Incidents[resolved_at]) ||
Incidents[resolved_at] > SelectedDate
)
&&
Incidents[state] <> "Canceled"
)
)Measure
Open Incidents (DistinctCount) =
VAR SelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT(Incidents[number]),
Incidents[opened_at] <= SelectedDate,
OR(
ISBLANK(Incidents[resolved_at]),
Incidents[resolved_at] > SelectedDate
),
Incidents[state] <> "Canceled",
ALL('Date')
)ALL('Date') prevents slicer from filtering opened_at/resolution dates. Counts tickets open as of SelectedDate.
6 Replies
Please try the measure below:
Open Incidents = VAR _SelectedDate = MAX ( 'Date'[Date] ) RETURN CALCULATE ( DISTINCTCOUNT ( Incidents[number] ), REMOVEFILTERS ( Incidents ), Incidents[opened_at] <= _SelectedDate, Incidents[resolved_at] > _SelectedDate || ISBLANK ( Incidents[resolved_at] ), Incidents[state] <> "Canceled" )Hi,
Can you try this?
Open Incidents =
VAR SelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT(Incidents[number]),
FILTER(
ALL(Incidents),
Incidents[opened_at] <= SelectedDate
&&
(
ISBLANK(Incidents[resolved_at]) ||
Incidents[resolved_at] > SelectedDate
)
&&
Incidents[state] <> "Canceled"
)
)Measure
Open Incidents (DistinctCount) =
VAR SelectedDate = MAX('Date'[Date])
RETURN
CALCULATE(
DISTINCTCOUNT(Incidents[number]),
Incidents[opened_at] <= SelectedDate,
OR(
ISBLANK(Incidents[resolved_at]),
Incidents[resolved_at] > SelectedDate
),
Incidents[state] <> "Canceled",
ALL('Date')
)ALL('Date') prevents slicer from filtering opened_at/resolution dates. Counts tickets open as of SelectedDate.
Hi qs1365
This is the measure that I use for your exact request, ticket backlog in ServiceNow
Incident Backlog = VAR CurrentDate = MAX('Date'[Date]) VAR ActiveTickets = CALCULATE(DISTINCTCOUNT(Incidents[Number]), ALL('Date'), 'Date'[Date]<=CurrentDate, ISBLANK(Incidents[Resolved Date]) ||Incidents[Resolved Date]>=CurrentDate) Return ActiveTickets--------------------------------
I hope this helps, please give kudos and mark as solved if it does!
Connect with me on LinkedIn.
Subscribe to my YouTube channel for Fabric/Power Platform related content!
Hi qs1365 ,
Thanks wardy912 , Kedar_Pande , cengizhanarslan and InsightsByV for the prompt respone.
I hope above provided solution helped in resolving the issue. If you have anything else to check we would be happy to address.
Thanks!!
