How to create a table to show number of days Trouble tickets take to clear

[Delguy87]

Hello, 

I have an Excel sheet of Trouble tickets with columns, 'Date Created' and 'Date Completed'.

Is there a way to generate a table to show the # of days it took to Complete the ticket. for example: 'Cleared in 7 days or less' Cleared in 8 to 30 days' 'Cleared in 31 to 60 days' and 'remaining open'.

I used the DATEDIFF([Date Created],[Date Completed],DAY), but was not able to get a table to show the example above.

[amitchandak · Accepted solution]

@Delguy87 , You have to create a column for that like

New column =

var _diff = DATEDIFF([Date Created],[Date Completed],DAY)

return

Switch(True() ,

_diff <=7 , "   Cleared in 7 Days or less",

_diff <=30 , "  Cleared in 8 to 30 days",

_diff <=60 , " Cleared in 31 to 60 days",

"remaining open"

)

Give space in text to sort it properly

[Delguy87]

See below, I had to remove MAX to get it to work. However, How do i get tickets closed on the same day to be apart of "cleared in 7 days or less". Its showing as "remaining open". 

Basically seeing 0 and blank values as the same. 

see below:

 

[v-tianyich-msft · Accepted solution]

Hi @Delguy87 ,

Awesome, @amitchandak  gave you a helpful answer, so you just need to tweak a few things based on his feedback:

Measure = var _diff = DATEDIFF(MAX('Table'[Start]),MAX('Table'[End]),DAY)
return Switch(True() ,
_diff = BLANK() , "no completed",
_diff <=7 , "   Cleared in 7 Days or less",
_diff <=30 , "  Cleared in 8 to 30 days",
_diff <=60 , " Cleared in 31 to 60 days",
_diff > 60 , " over 60 days"
)

Hope it helps!

Best regards,
Community Support Team_ Scott Chang

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

[amitchandak · Accepted solution]

@Delguy87 , You have to create a column for that like

New column =

var _diff = DATEDIFF([Date Created],[Date Completed],DAY)

return

Switch(True() ,

_diff <=7 , "   Cleared in 7 Days or less",

_diff <=30 , "  Cleared in 8 to 30 days",

_diff <=60 , " Cleared in 31 to 60 days",

"remaining open"

)

Give space in text to sort it properly

[Delguy87]

Thank you! I'm very close. the only issue I'm having now is that 'remaining open' results are filling in for tickets closed 60 days or more and tickets that are open with no completed dates. how would I set it up so that tickets will not show 'remaining open' for those two conditions?