Pending Tickets/Backlog Count for Distribution Analysis

[marlinajamal]

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_NUMBER	CREATED_DATE	CLOSED_DATETIME	LOCATION CODE	Pending Tickets (Overall)	Pending Tickets (Per Location)
1-34415354673	1/6/2020 9:22	3/7/2020 9:59	S003	0	0
1-22933800909	1/6/2020 10:25	1/7/2020 17:38	S004	1	0
1-34426852255	1/6/2020 11:37	1/7/2020 12:43	S001	2	0
1-34429503918	1/6/2020 13:14	10/7/2020 20:35	S005	3	0
1-34429366814	1/6/2020 13:42	7/7/2020 14:51	S001	4	1
1-22935228732	1/6/2020 14:12	6/7/2020 11:20	S001	5	2
1-34439286922	1/6/2020 20:18	10/7/2020 20:10	S005	6	1
1-34439415415	1/6/2020 20:29	3/7/2020 14:41	S001	7	3
1-34441808655	1/6/2020 23:37	6/7/2020 20:32	S003	8	1
1-34971269691	1/7/2020 0:01	1/7/2020 16:19	S005	9	2
1-34971954911	1/7/2020 0:06	1/7/2020 10:45	S002	10	0
INC0002105223	1/7/2020 0:11	12/7/2020 8:33	S006	11	0
INC0002105242	1/7/2020 0:14	12/7/2020 8:33	S007	12	0
1-23056555127	1/7/2020 0:15	1/7/2020 17:24	S008	13	0
1-34972860319	1/7/2020 0:15	4/7/2020 5:32	S006	14	2
1-34972542750	1/7/2020 0:16	1/7/2020 20:16	S001	15	4
INC0002105274	1/7/2020 0:24	12/7/2020 8:33	S006	16	1
INC0002105275	1/7/2020 0:24	5/7/2020 1:57	S006	17	2
1-34970507931	1/7/2020 0:24	1/7/2020 16:24	S004	18	1
1-23056048995	1/7/2020 0:27	1/7/2020 12:44	S009	19	0

[v-alq-msft · Accepted solution]

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.

[marlinajamal]

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

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 

[amitchandak]

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

[marlinajamal]

@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

[amitchandak]

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

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