Age of Open Ticket in Considering Tickets created in any month

[ashaikh]

 Hello All,

I am looking to calculate how many days a particular ticket is open as of today, irrespective of when the ticket was created.

For example, if I am creating a weekly report for last week so I want to show how many tickets are still open.

Since the report is for last week I will have report filter for last week so report show ticket created on in the last week, but there could be ticket which are created week prior which needs to be shown as well

Here is the example - 

IncidentNumber	|SubmitDate	|LastResolvedDate|
================|===============|================|
INC000000770560	|09/09/2018 4:18|09/09/2018 9:42 |
INC000000770561	|09/10/2018 4:18|09/10/2018 9:42 |
INC000000770725	|09/11/2018 4:18|                |   
INC000000770729	|09/12/2018 4:18|09/12/2018 9:42 |
INC000000770730	|09/13/2018 4:18|09/13/2018 9:42 |
INC000000770731	|09/14/2018 4:18|                |
INC000000770732	|09/15/2018 4:18|09/15/2018 9:42 |
INC000000770733	|09/02/2018 4:18|09/02/2018 9:57 |
INC000000770734	|09/03/2018 4:18|09/03/2018 9:57 |
INC000000770735	|09/04/2018 4:18|09/04/2018 9:57 |
INC000000770738	|09/05/2018 4:18|                |
INC000000770741	|09/06/2018 4:18|09/06/2018 9:57 |
INC000000770742	|09/07/2018 4:18|	         |

Now I am creating report for week 9/9/18 - 9/15/18 so tickets from week prior are also open. In this case ticketage for current date say 9/16/18 will be as below.

IncidentNumber	|SubmitDate	|LastResolvedDate|TicketAge|
================|===============|================|=========|
INC000000770560	|09/09/2018 4:18|09/09/2018 9:42 |         |
INC000000770561	|09/10/2018 4:18|09/10/2018 9:42 |         |
INC000000770725	|09/11/2018 4:18|                |5        |    
INC000000770729	|09/12/2018 4:18|09/12/2018 9:42 |         |
INC000000770730	|09/13/2018 4:18|09/13/2018 9:42 |         |
INC000000770731	|09/14/2018 4:18|                |2        |
INC000000770732	|09/15/2018 4:18|09/15/2018 9:42 |         |
INC000000770733	|09/02/2018 4:18|09/02/2018 9:57 |         | 
INC000000770734	|09/03/2018 4:18|09/03/2018 9:57 |         |
INC000000770735	|09/04/2018 4:18|09/04/2018 9:57 |         |
INC000000770738	|09/05/2018 4:18|                |10       |
INC000000770741	|09/06/2018 4:18|09/06/2018 9:57 |         |
INC000000770742	|09/07/2018 4:18|	         |9        |

So now the the result should be shown as below:

Ticket Age	                |Count|
================================|=====|  
Ticket Open < 1 day	        |0    |
Ticket Open > 1 day and <= 4 day|1    |
Ticket Open > 4 day and <= 7 day|1    |
Ticket Open > 7 day	        |2    |

Any help will be appreciated thanks

[Greg_Deckler]

Take a look at these two Quick Measures as I think you want something like them.

https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365

[ashaikh]

@Greg_Deckler Thanks for the help.

But with this how could I show the result in following format

Ticket Age	                |Count|
================================|=====|  
Ticket Open < 1 day	        |0    |
Ticket Open > 1 day and <= 4 day|1    |
Ticket Open > 4 day and <= 7 day|1    |
Ticket Open > 7 day	        |2    |

[Greg_Deckler]

What I would probably do would be to create an Enter Data query that consists of a single column with your labels in it. Then I would create a measure with a SWITCH statement that checks what label is present and makes the appropriate calculation based upon the age of the ticket, so something like:

Measure = 
VAR __label = MAX([Ticket Age])
RETURN
SWITCH(
  TRUE(),
  __label = "Ticket Open < day",COUNTROWS(FILTER(ALL('Table'),[Age]<1)),
  __label = "Ticket Open > 1 day and <= 4 day",COUNTROWS(FILTER(ALL('Table'),[Age]>=1 && [Age]<=4,
...
)

[ashaikh]

Thanks @Greg_Deckler

This worked perfectly. I just had one question if I want to show the ticket age based on a category what modification do I need to do for that.

Thanks in Advance

[ashaikh]

Will this measure be based on previous query you provided?

Because my main concern here is getting all open ticket irrespective of month that report is baed on.