Help calculating a measure of current non compliant records with minimal risk

[Sonnet]

Hi everyone, 

I'd like your help with a measure to calculate current non compliant records with minimal risk. A record with minimal risk is non compliant if the date closed is over 365 days from the date opened and teh risk description is minimal. 

This is the measure I use:

CALCULATE( COUNTX(FILTER('AppendWebVPentest',
'AppendWebVPentest'[Date Opened]<= MAX('Calendar'[Date]) &&
'AppendWebVPentest'[Risk Description] = "minimal"
&& (ISBLANK('AppendWebVPentest'[Date Closed])
||'AppendWebVPentest'[Date Closed] > DATEADD('AppendWebVPentest'[Date Opened],+365,DAY)
&& AppendWebVPentest[Date Closed]>MAX('Calendar'[Date]))),[Title]),
CROSSFILTER('AppendWebVPentest'[Date Opened],'Calendar'[Date],None)) 

The issue I am having is that if today the date closed for a record is blank and the date opened is 01/01/2024 and the risk description is minimal, from the measure above I get a 1, which means that is an open record, but it doesn't calculate if it is compliant (I want also to calculate that 365 days have passed from the date opened when the date closed is blank on a specific date in time).

This is the issue in the table:

The SoMMin append is 1, should be 0 because 365 days haven't passed since the date opened and risk description being minimal.

Any suggestion is very welcome.

[lbendlin]

use COALESCE to substitute missing Close Date values with TODAY().

[Sonnet]

Hi, thanks for the replay. Could you please show me how and where to use coalesce in my measure?

[lbendlin]

CALCULATE( COUNTX(FILTER('AppendWebVPentest',
'AppendWebVPentest'[Date Opened]<= MAX('Calendar'[Date]) &&
'AppendWebVPentest'[Risk Description] = "minimal"
&& (ISBLANK('AppendWebVPentest'[Date Closed])
||COALESCE('AppendWebVPentest'[Date Closed],TODAY()) > DATEADD('AppendWebVPentest'[Date Opened],+365,DAY)
&& COALESCE('AppendWebVPentest'[Date Closed],TODAY())>MAX('Calendar'[Date]))),[Title]),
CROSSFILTER('AppendWebVPentest'[Date Opened],'Calendar'[Date],None)) 

[Sonnet]

 Thanks, but it doesn't work. From the screenshot below, the measure should return 0 because that record is within 1 year past the Date Opened and not over.

[lbendlin]

I only showed you the mechanics.  I have no insights into your business logic.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[Sonnet]

Thanks for your help.

Please consider the below sample data table to work a solution.

ID	Name	Opening Date	Completion Date	Task Type
PT00001	Name 00001	24/08/2023	01/02/2024	Project
PT00002	Name 00002	11/09/2023	28/09/2023	Task		Project	180 days
PT00003	Name 00003	22/09/2023	24/12/2023	Assignment		Task	14 days
PT00004	Name 00004	13/10/2023	23/10/2023	Support Ticket		Assignment	90 days
PT00005	Name 00005	11/09/2023	09/01/2024	Project		Support Ticket	3 days
PT00006	Name 00006	06/10/2023	18/10/2024	Task
PT00007	Name 00007	19/10/2023	02/02/2024	Assignment
PT00008	Name 00008	16/11/2023	20/11/2023	Support Ticket
PT00009	Name 00009	17/11/2023		Project
PT00010	Name 00010	20/11/2023	21/11/2023	Support Ticket
PT00011	Name 00011	21/11/2023	01/12/2023	Support Ticket
PT00012	Name 00012	22/11/2023	22/11/2023	Support Ticket
PT00013	Name 00013	09/12/2023		Project
PT00014	Name 00014	24/12/2023	09/01/2024	Task
PT00015	Name 00015	18/01/2024		Assignment
PT00016	Name 00016	20/01/2024	28/01/2024	Support Ticket
PT00017	Name 00017	10/01/2024	20/01/2024	Support Ticket
PT00018	Name 00018	31/01/2024	11/04/2024	Task
PT00019	Name 00019	22/02/2024		Assignment

[lbendlin]

Based on your sample data I arrived at this step:

What do you want to do next?

[Sonnet]

@lbendlin I appreaciate your help, thank you.

What I want to achieve is that, based on the task type (columns from the sample data table) and the days to complete it, to show how many tasks were completed after the established days in a specific period of time.

For example, using this record fromt he above table:

PT00007

Name 00007

19/10/2023

02/02/2024

Assignment

This record by the end of January 2024 had a blank value on the closed date column (because it wasn't yet completed) and so it was overdue, meaning that it was over 90 days. Therefore, the measure should flag that record as being overdue. 

Similarly, in February 2024, the same record is overdue having as value 02/02/2024 for the date closed.
I hope I was clear.

[lbendlin]

I don't claim to understand what you are after.