cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
New Member

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.

I would like to study the distribution for count pending tickets for each new tickets per location.

below is the sample data and intended collumns are the last 2.

 

Appreciate some help

 

TT_NUMBERCREATED_DATECLOSED_DATETIMELOCATION CODEPending Tickets (Overall)Pending Tickets (Per Location)
1-344153546731/6/2020 9:223/7/2020 9:59S00300
1-229338009091/6/2020 10:251/7/2020 17:38S00410
1-344268522551/6/2020 11:371/7/2020 12:43S00120
1-344295039181/6/2020 13:1410/7/2020 20:35S00530
1-344293668141/6/2020 13:427/7/2020 14:51S00141
1-229352287321/6/2020 14:126/7/2020 11:20S00152
1-344392869221/6/2020 20:1810/7/2020 20:10S00561
1-344394154151/6/2020 20:293/7/2020 14:41S00173
1-344418086551/6/2020 23:376/7/2020 20:32S00381
1-349712696911/7/2020 0:011/7/2020 16:19S00592
1-349719549111/7/2020 0:061/7/2020 10:45S002100
INC00021052231/7/2020 0:1112/7/2020 8:33S006110
INC00021052421/7/2020 0:1412/7/2020 8:33S007120
1-230565551271/7/2020 0:151/7/2020 17:24S008130
1-349728603191/7/2020 0:154/7/2020 5:32S006142
1-349725427501/7/2020 0:161/7/2020 20:16S001154
INC00021052741/7/2020 0:2412/7/2020 8:33S006161
INC00021052751/7/2020 0:245/7/2020 1:57S006172
1-349705079311/7/2020 0:241/7/2020 16:24S004181
1-230560489951/7/2020 0:271/7/2020 12:44S009190
1 ACCEPTED SOLUTION
Community Support
Community Support

Hi, @marlinajamal 

 

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

Table:

b1.png

 

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:

b2.png

 

Best Regards

Allan

 

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

View solution in original post

6 REPLIES 6
Community Support
Community Support

Hi, @marlinajamal 

 

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

Table:

b1.png

 

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:

b2.png

 

Best Regards

Allan

 

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

View solution in original post

@v-alq-msft ,

 

Tq so much. You given me a starting point. 

The CLOSED_DATE TIME should also be one of the parameters, so i add it to the formula and edit the 3rd ticket (1st for S001) CLOSED DATE TIME to be closed before the creation of the 5th tickets for S001 to make sure the formula works.

 

and it works!

 

The formula :

 

Pending Tickets (Overall) = 
var result=
CALCULATE(
    DISTINCTCOUNT('Table'[TT_NUMBER]),
    FILTER(
        ALL('Table'),
        'Table'[CREATED_DATE]<EARLIER('Table'[CREATED_DATE]) && 
        'Table'[CLOSED_DATETIME]>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'[CLOSED_DATETIME]>EARLIER('Table'[CREATED_DATE])&&
        'Table'[LOCATION CODE]=EARLIER('Table'[LOCATION CODE])
    )
)
return
IF(
    ISBLANK(result),
    0,
    result
)

 

 

The result:

 

Screenshot 2020-09-23 115303.png

So thank you so much for your help!

Been stuck for a few weeks tbh

 

Edited file here

Pending Tickets Backlog Count for Distribution Analysis_edited.pbix 

 

Super User IV
Super User IV

@marlinajamal , What is the expected output?

You can create date like

CREATED_DATE Date = [CREATED_DATE].date
CLOSED_DATE = [CLOSED_DATE].Date

And join them with date table and One active join. One inactive join. You can use inactive join with help from userelation. Refer:https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

 

 

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@amitchandak , the output is calculated collumn that gives value as per the last 2 collumn.

Pending Tickets (Overall) - when no filter selected

Pending Tickets Per State - when State Code selected as filter

@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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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.

 

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors