Hi ,
How to show how many tickets in Created, Closed and In progress tickets in table format , below I attached screen shot of sample one. Created for Submitted Date and Closed for Resolved/ Completed Date .
| IPC dump based on Submit Date | Region | Account | Ticket Type | Ticket Number | Priority | Status | Assigned Support Group (if available) | Submit Date | Resolved / Completed Date | Closed Date | Scheduled Start Date | Scheduled End date | Change Success/fail | Coordinator Name | Domain | Problem type | Status reason | Category | Sub BU | Priorty2 | Ageing |
| IPC dump based on Submit Date | Germany | PY | Incident | INC00000024689 | medium | Resolved | Database - Oracle | 7/1/2024 | Monday, July 1, 2024 | Database - Oracle | P3 | EU2 | P3 & P4 | 30days | |||||||
| IPC Backlog (Open tickets) data | Germany | PY | Incident | CRQ000000007542 | low | Implementation In Progress | Windows | 6/4/2024 | SCCM Operations | P4 | EU2 | P3 & P4 | 30days | ||||||||
| IPC dump based on Closed Date | Europe | Sh | Incident | INC118272071 | high | Resolved | Non-core team | 6/30/2024 | Monday, July 1, 2024 | Monday, July 1, 2024 | Network | P2 | EU2 | P1 & P2 | 30days | ||||||
| IPC dump based on Closed Date | Europe | Sh | Incident | INC119282056 | critical | Resolved | Microsoft | 7/18/2024 | Tuesday, July 23, 2024 | Tuesday, July 23, 2024 | Application | P1 | EU2 | P1 & P2 | 30days |
- Incident Management P1/P2 - MTD Status: (Created, Closed and In progress incident High and Critical for P1 & P2 tickets).
- 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
- Incident Management P3/P4 - MTD Status: (Created, Closed and In progress incident Medium and Low for P3 & P4 tickets).
- 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
- Incident Management P1/P2 - Last 7 Days Status: (Created, Closed and In progress incident High and Critical for P1 & P2 tickets).
- 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
- Incident Management P3/P4 - Last 7 Days Status: (Created, Closed and In progress incident Medium and Low for P3 & P4 tickets).
- 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
- Incident Management P1/P2 - Last 24 Hours Status: (Created, Closed and In progress incident High and Critical for P1 & P2 tickets).
- 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
- Incident Management P3/P4 - Last 24 Hours Status: (Created, Closed and In progress incident Medium and Low for P3 & P4 tickets).
- 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
1 ACCEPTED SOLUTION
Hi @Pavani ,
Here are the steps you can follow：
1. Create calculated column.
Ageing =
var _if=
IF(
'Table'[Status]="Implementation In Progress",
DATEDIFF('Table'[Submit Date],TODAY(),DAY),
IF(
'Table'[Closed Date]=BLANK(),
DATEDIFF('Table'[Submit Date],'Table'[Resolved / Completed Date],DAY),
DATEDIFF('Table'[Submit Date],'Table'[Closed Date],DAY) ))
return
SWITCH(
TRUE(),
_if>=0&&_if<=7,"0-7Days",
_if>7&&_if<=15,"Is>7Days",
_if>15&&_if<=30,"Is>15Days",
"Is>30Days")
2. Enter data – Table.
3. Because the default Power BI sorting is alphabetical, in order to prevent the appearance of the sorting does not meet your requirements, we can sort according to [Index] to sort.
4. Create measure.
Measure =
SWITCH(
TRUE(),
MAX('Table2'[Group]) = "Created",COUNTX(FILTER('Table','Table'[Submit Date]<>BLANK()),[Domain]),
MAX('Table2'[Group]) = "Closed",COUNTX(FILTER('Table','Table'[Status]="Resolved"),[Domain]),
MAX('Table2'[Group]) = "In Progress",COUNTX(FILTER('Table','Table'[Status]="Implementation In Progress"),[Domain]),
COUNTX(
FILTER('Table',
'Table'[Ageing]=MAX('Table2'[Group])),[Ageing]))5. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi @Pavani ,
Here are the steps you can follow：
1. Create calculated column.
Ageing =
var _if=
IF(
'Table'[Status]="Implementation In Progress",
DATEDIFF('Table'[Submit Date],TODAY(),DAY),
IF(
'Table'[Closed Date]=BLANK(),
DATEDIFF('Table'[Submit Date],'Table'[Resolved / Completed Date],DAY),
DATEDIFF('Table'[Submit Date],'Table'[Closed Date],DAY) ))
return
SWITCH(
TRUE(),
_if>=0&&_if<=7,"0-7Days",
_if>7&&_if<=15,"Is>7Days",
_if>15&&_if<=30,"Is>15Days",
"Is>30Days")2. Enter data – Table.
3. Because the default Power BI sorting is alphabetical, in order to prevent the appearance of the sorting does not meet your requirements, we can sort according to [Index] to sort.
4. Create measure.
Measure =
SWITCH(
TRUE(),
MAX('Table2'[Group]) = "Created",COUNTX(FILTER('Table','Table'[Submit Date]<>BLANK()),[Domain]),
MAX('Table2'[Group]) = "Closed",COUNTX(FILTER('Table','Table'[Status]="Resolved"),[Domain]),
MAX('Table2'[Group]) = "In Progress",COUNTX(FILTER('Table','Table'[Status]="Implementation In Progress"),[Domain]),
COUNTX(
FILTER('Table',
'Table'[Ageing]=MAX('Table2'[Group])),[Ageing]))5. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi ,
Thank you so much for your help and one more help below screen shot it is showing total count of P1&P2 tickets but here I need to show MTD One status, Last 7 Das Status and Last 24 Hours status. how we can create. Thank you!
