How to create a dax Created MTD , Last 7 Days and Last 24 hours

[Pavani]

Hi ,

Please help to create a DAX as per below request .

Incident Data P1 & P2 tickets Table: ( MTD Status)

Created Tickets: Created data account, ticket type incident, Priority High and critical (P1 & P2), MTD data for Submit Date. 

Closed Tickets : Closed data account, ticket type incident, Priority High and critical (P1 & P2), MTD data for Resolved / Completed Date.

  In Progress Tickets : Open data account, ticket type incident, Priority High and critical (P1 & P2).

0-7 Days = Open data account, ticket type incident, Priority High and critical (P1 & P2), Aging Days less than 8 days.

Is >7 Days = Open data account, ticket type incident, Priority High and critical (P1 & P2), Aging Days Greater than 8 days less than 16 Days.

Is  > 15 Days = Open data account, ticket type incident, Priority High and critical (P1 & P2), Aging Days Greater than 16 days less than 31 Days.

Is >31 Days = Open data account, ticket type incident, Priority High and critical (P1 & P2), Aging Days Greater than 31 days.

Regards,

Pavani. K

[dharmendars007 · Accepted solution]

Hello @Pavani , 

You have to create 7 measures for like the below...

1. For Created Tickets below similarly create for Cosed and Inprogreess by changing the filter.

CreatedTickets =
CALCULATE(
COUNTROWS('Incident Data'),
'Incident Data'[Ticket Type] = "Incident",
'Incident Data'[Priority] IN {"P1", "P2"},
MONTH('Incident Data'[Submit Date]) = MONTH(TODAY()))

2. For Aging Tickets you need to apply various filter and create logic I have creacted for couple fo them please try to creat for other with simi;ar logics by changing the Days bucket accordingly

Open0to7Days =
CALCULATE(
COUNTROWS('Incident Data'),
'Incident Data'[Ticket Type] = "Incident",
'Incident Data'[Priority] IN {"P1", "P2"},
DATEDIFF('Incident Data'[Submit Date], TODAY(), DAY) <= 7,
ISBLANK('Incident Data'[Resolved Date]))

OpenGreaterThan31Days =
CALCULATE(
COUNTROWS('Incident Data'),
'Incident Data'[Ticket Type] = "Incident",
'Incident Data'[Priority] IN {"P1", "P2"},
DATEDIFF('Incident Data'[Submit Date], TODAY(), DAY) > 31,
ISBLANK('Incident Data'[Resolved Date]))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN 

[v-kongfanf-msft · Accepted solution]

Hi @Pavani ,

Maybe you can try formula like below to create measure:

Aging0to7Days = 
CALCULATE(
    COUNTROWS('Incident Data'),
    'Incident Data'[Ticket Type] = "incident",
    'Incident Data'[Priority] IN {"High", "Critical"},
    'Incident Data'[Aging Days] < 8
)

Aging8to15Days = 
CALCULATE(
    COUNTROWS('Incident Data'),
    'Incident Data'[Ticket Type] = "incident",
    'Incident Data'[Priority] IN {"High", "Critical"},
    'Incident Data'[Aging Days] >= 8,
    'Incident Data'[Aging Days] <= 15
)

Aging16to31Days = 
CALCULATE(
    COUNTROWS('Incident Data'),
    'Incident Data'[Ticket Type] = "incident",
    'Incident Data'[Priority] IN {"High", "Critical"},
    'Incident Data'[Aging Days] > 15,
    'Incident Data'[Aging Days] <= 31
)

Best Regards,
Adamk Kong

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

[SachinNandanwar]

I guess it would require to create 7 measures.

[Pavani]

Yes, How I need to create DAX for this requirements , please help on to resolve.

Thanks! 

[dharmendars007 · Accepted solution]

Hello @Pavani , 

You have to create 7 measures for like the below...

1. For Created Tickets below similarly create for Cosed and Inprogreess by changing the filter.

CreatedTickets =
CALCULATE(
COUNTROWS('Incident Data'),
'Incident Data'[Ticket Type] = "Incident",
'Incident Data'[Priority] IN {"P1", "P2"},
MONTH('Incident Data'[Submit Date]) = MONTH(TODAY()))

2. For Aging Tickets you need to apply various filter and create logic I have creacted for couple fo them please try to creat for other with simi;ar logics by changing the Days bucket accordingly

Open0to7Days =
CALCULATE(
COUNTROWS('Incident Data'),
'Incident Data'[Ticket Type] = "Incident",
'Incident Data'[Priority] IN {"P1", "P2"},
DATEDIFF('Incident Data'[Submit Date], TODAY(), DAY) <= 7,
ISBLANK('Incident Data'[Resolved Date]))

OpenGreaterThan31Days =
CALCULATE(
COUNTROWS('Incident Data'),
'Incident Data'[Ticket Type] = "Incident",
'Incident Data'[Priority] IN {"P1", "P2"},
DATEDIFF('Incident Data'[Submit Date], TODAY(), DAY) > 31,
ISBLANK('Incident Data'[Resolved Date]))

If you find this helpful , please mark it as solution which will be helpful for others and Your Kudos/Likes are much appreciated!

Thank You

Dharmendar S

LinkedIN