topic Re: How to create a dax Created MTD , Last 7 Days and Last 24 hours in DAX Commands and Tips

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

Pavani — Fri, 27 Sep 2024 16:26:31 GMT

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

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

SachinNandanwar — Fri, 27 Sep 2024 17:23:07 GMT

I guess it would require to create 7 measures.

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

Pavani — Fri, 27 Sep 2024 18:12:30 GMT

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

Thanks!

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

dharmendars007 — Sat, 28 Sep 2024 07:51:00 GMT

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

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

Anonymous — Mon, 30 Sep 2024 07:59:28 GMT

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.