How to Calculate Created, Closed and In progress incidents in DAX

[Pavani]

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

1. Incident Management P1/P2 - MTD Status: (Created, Closed and In progress incident High and Critical for P1 & P2 tickets).
2. 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
3. Incident Management P3/P4 - MTD Status: (Created, Closed and In progress incident Medium and Low for P3 & P4 tickets).
4. 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
5. Incident Management P1/P2 - Last 7 Days Status: (Created, Closed and In progress incident High and Critical for P1 & P2 tickets).
6. 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
7. Incident Management P3/P4 - Last 7 Days Status: (Created, Closed and In progress incident Medium and Low for P3 & P4 tickets).
8. 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
9. Incident Management P1/P2 - Last 24 Hours Status: (Created, Closed and In progress incident High and Critical for P1 & P2 tickets).
10. 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .
11. Incident Management P3/P4 - Last 24 Hours Status: (Created, Closed and In progress incident Medium and Low for P3 & P4 tickets).
12. 0-7 Days, 'Is >7 Days, Is >15 Days and Is >30 Days for open data .

[Pavani]

Thank you so much Its working . 

[Anonymous · 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

[Pavani]

Hi , Any help the below request. Thanks!

[Pavani]

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!