topic Re: Help calculating a measure of current non compliant records with minimal risk in DAX Commands and Tips

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

Sonnet — Mon, 26 Feb 2024 17:28:24 GMT

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.

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

lbendlin — Tue, 27 Feb 2024 01:08:23 GMT

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

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

Sonnet — Tue, 27 Feb 2024 08:42:47 GMT

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

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

lbendlin — Tue, 27 Feb 2024 16:20:14 GMT

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))

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

Sonnet — Tue, 27 Feb 2024 17:28:30 GMT

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.

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

lbendlin — Tue, 27 Feb 2024 17:52:27 GMT

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-Forum/ba-p/963216

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/1447523

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

Sonnet — Tue, 27 Feb 2024 19:36:49 GMT

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

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

lbendlin — Wed, 28 Feb 2024 01:26:18 GMT

Based on your sample data I arrived at this step:

What do you want to do next?

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

Sonnet — Wed, 28 Feb 2024 08:59:16 GMT

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.

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

lbendlin — Thu, 29 Feb 2024 00:35:18 GMT

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