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.

v-alq-msft's avatar
v-alq-msft
Community Support
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.

  • marlinajamal's avatar
    marlinajamal
    New Member
    5 years ago

    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