Forum Discussion

JimLee's avatar
JimLee
Icon for Helper I rankHelper I
5 years ago
Solved

Charting Aging Tickets Over Time

I am having trouble charting aging tickets over time. I can do it in Excel, but I cannot figure out how to do it in Power BI. Here is what the data look like:   I can create a table in Excel ...
Need Help
  • JimLee's avatar
    JimLee
    5 years ago

    I figured it out! Days was not returning partial days, so I had to use hours and divide by 1440. 

    Now the problem is the calculations are too hard for my computer's resources! Pardon the messy DAX. 

     

    Average Ticket Age = AVERAGEX('Incidents',
    IF(
    	DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)<0, //Submit after reporting date is a neg #, so ticket is not open yet
    "",
        IF(
            ISBLANK('Incidents'[Last Resolved Date]),//if the resoved date is blank, then it is max date minus submit date
        DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)/1440,
            IF(
                'Incidents'[Last Resolved Date]>MAX(Dates[Date and Time]),
            DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)/1440,
                IF(
                    'Incidents'[Last Resolved Date]+1>MAX(Dates[Date and Time]),DATEDIFF('Incidents'[Submit Date],'Incidents'[Last Resolved Date],MINUTE)/1440,
                ""//If the resolved date is greater than the report date then the age is the report date minus submit date 
            
                )
            )
        )
    ))

     

JimLee's avatar
JimLee
Icon for Helper I rankHelper I
5 years ago

v-janeyg-msft 

 

Thank you for helping clean up my ticket count.

 

What I really need is to be able to track the ticket's age over time. 

 

For example, in the data is INC000003301893 that was opened on Oct 3. I would like to be able to calculate is how old that ticket is on Oct 4, Oct 5, Oct 6, and so on. This will allow me to get the average ticket age for all open tickets during each period. 

Is there a way to do that? 

v-janeyg-msft's avatar
v-janeyg-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, JimLee 

I don’t know why you use crossfilter, you can try this if it works:

Open Incidents = 
CALCULATE (
    COUNTX (
        FILTER (
            'Incident Table',
            'Incident Table'[Submit Date] <= MAX ( 'Dates'[Date] )
           && 'Incident Table'[Submit Date] >= MIN ( 'Dates'[Date] )
                && OR (
                    ISBLANK ( 'Incident Table'[Last Resolved Date] ),
                    'Incident Table'[Last Resolved Date] > MAX ( 'Dates'[Date] )
                )
        ),
        'Incident Table'[Incident Number]
    )
    //,
   // CROSSFILTER ( 'Incident Table'[Submit Date], 'Dates'[Date], NONE )
)
avg = DIVIDE([Open Incidents],DISTINCTCOUNT(Dates[Date]))

If the problem isn’t solved,please feel free to ask me.

 

Best Regards

Janey Guo

 

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

 

  • JimLee's avatar
    JimLee
    Icon for Helper I rankHelper I
    5 years ago

    v-janeyg-msft 

     

    Thanks for continuing to try and help. 

     

    The Open Incidents yielded zero results. I commented out this line and got the right counts:

    && 'MITSC RES - Last 6 Months Incid'[Submit Date] >= MIN ( 'Dates'[Date] )

     

    Unfortunately, this gives the average count of tickets, but it does not work to get the average age of the tickets:

    avg = DIVIDE([Open Incidents],DISTINCTCOUNT(Dates[Date]))

     

    I need to be able to do something like a DATEDIFF using 'Dates'[Date] and [Submit Date], but I cannot figure out how to do that. 

    • JimLee's avatar
      JimLee
      Icon for Helper I rankHelper I
      5 years ago

      I figured it out! Days was not returning partial days, so I had to use hours and divide by 1440. 

      Now the problem is the calculations are too hard for my computer's resources! Pardon the messy DAX. 

       

      Average Ticket Age = AVERAGEX('Incidents',
    IF(
    	DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)<0, //Submit after reporting date is a neg #, so ticket is not open yet
    "",
        IF(
            ISBLANK('Incidents'[Last Resolved Date]),//if the resoved date is blank, then it is max date minus submit date
        DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)/1440,
            IF(
                'Incidents'[Last Resolved Date]>MAX(Dates[Date and Time]),
            DATEDIFF('Incidents'[Submit Date],MAX(Dates[Date and Time]),MINUTE)/1440,
                IF(
                    'Incidents'[Last Resolved Date]+1>MAX(Dates[Date and Time]),DATEDIFF('Incidents'[Submit Date],'Incidents'[Last Resolved Date],MINUTE)/1440,
                ""//If the resolved date is greater than the report date then the age is the report date minus submit date 
            
                )
            )
        )
    ))