Creating Open Tickets / Backlog Evolution Measure & Graph Visual

[qs1365]

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?

Open Incidents (DistinctCount) =

VAR SelectedDate =

    MAX('Date'[Date])

RETURN

CALCULATE(

    DISTINCTCOUNT(Incidents[number]),

    Incidents[opened_at] <= SelectedDate,

    Incidents[resolved_at] > SelectedDate

        || ISBLANK(Incidents[resolved_at]),

    Incidents[state] <> "Canceled",

    USERELATIONSHIP('Date'[Date], Incidents[opened_at])

)

[InsightsByV · Accepted solution]

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"
)
)

[v-sathmakuri]

Hi @qs1365 ,

Could you please let us know if you still have any issues?

Thanks!!

[v-sathmakuri]

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!!

[wardy912]

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!

[Kedar_Pande · Accepted solution]

@qs1365 

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.

[cengizhanarslan]

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"
    )

[InsightsByV · Accepted solution]

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"
)
)