Forum Discussion

marlinajamal's avatar
marlinajamal
New Member
5 years ago
Solved

Pending Tickets/Backlog Count for Distribution Analysis

Hi,   Im a noob in Power BI. Just started about 2 months ago on and off.   I have data for closed tickets. Each line is unique ticket ID with created date time, closed date time, location code. ...
Need Help
  • v-alq-msft's avatar
    v-alq-msft
    5 years ago

    Hi, marlinajamal 

     

    Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

    Table:

     

    You may create two calculated columns as below.

    Pending Tickets (Overall) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

Pending Tickets (Per Location) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE])&&
        'Table'[LOCATION CODE]=EARLIER('Table'[LOCATION CODE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

     

    Result:

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak's avatar
amitchandak
Super User
5 years ago

marlinajamal , To me first column (out of 2 last) seems like an index column. It is not going up down based on some logic.  What is the logic for that?

 

Second column. You can not create a column that can use slicer values.

marlinajamal's avatar
marlinajamal
New Member
5 years ago

amitchandak 

the logic or the formula for the first collumn is

 

count of tickets still opened at the time of creation of new ticket. Any tickets with Created Date Time older AND Closed Date Time newer than than the reference ticket's Created Date Time. 

 

For Collumn (Pending Tickets Overall)

for line one, there are no other tickets created before hence, 0.

For line 2, theres 1 ticket open previously(older creation date) and not yet closed(newer closed date), hence 1.

and so on.

 

maybe the sample is not really good as it didnt demonstrate the pending tickets count may go down if there's ticket closed before the creation of the reference ticket. 

 

Hope this clarify.