Calculating distinct count of actions that are open for more than 30 days

CarryASaw — Fri, 15 Mar 2024 03:25:40 GMT

Hi, I am trying to calculate the actions that are open for more than 30 days.

Below are the code that I created for calculation: It doesnt work and giving me the wrong calculation.

OverDueActionHigh Open30d = CALCULATE(DISTINCTCOUNT(EventsAction[Id]), Filter(EventsAction, EventsAction[Finding Date] + 30 //days?? && EventsAction[Criticality] = "High" && EventsAction[Status] = "Open - In Progress" ) )

I just want to count how many open actions that are still open for more than 30 days from the finding date.

This is my table that Im using.
Table: EventsAction

Id	Criticality	Status	Finding Date
13232	High	Open - In Progress	1/10/2023
1442	High	Open - In Progress	27/11/2023
122451	High	Closed	9/08/2023
22423	Moderate	Closed	10/08/2023
24552	Low	Open - In Progress	7/01/2024
3335261	Low	Closed	10/08/2023
3552	High	Open - In Progress	10/11/2023
6696	High	Open - In Progress	12/03/2024

Note that Id 6696 is not yet overdue as it was only created recently.

The result I need to get should be:

Re: Calculating distinct count of actions that are open for more than 30 days

Jihwan_Kim — Fri, 15 Mar 2024 04:30:34 GMT

Hi,

Please try something like below.

OverDueActionHigh Open30d = CALCULATE ( DISTINCTCOUNT ( EventsAction[Id] ), FILTER ( EventsAction, TODAY () - EventsAction[Finding Date] > 30 && EventsAction[Criticality] = "High" && EventsAction[Status] = "Open - In Progress" ) )

topic Re: Calculating distinct count of actions that are open for more than 30 days in DAX Commands and Tips

Calculating distinct count of actions that are open for more than 30 days

Re: Calculating distinct count of actions that are open for more than 30 days