cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

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

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]>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.

9 REPLIES 9
Super User

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

Helper I

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

Super User
``````CALCULATE( COUNTX(FILTER('AppendWebVPentest',
'AppendWebVPentest'[Date Opened]<= MAX('Calendar'[Date]) &&
'AppendWebVPentest'[Risk Description] = "minimal"
&& (ISBLANK('AppendWebVPentest'[Date Closed])
&& COALESCE('AppendWebVPentest'[Date Closed],TODAY())>MAX('Calendar'[Date]))),[Title]),
CROSSFILTER('AppendWebVPentest'[Date Opened],'Calendar'[Date],None)) ``````
Helper I

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.

Super User

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.

Helper I
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
Super User

Based on your sample data I arrived at this step:

What do you want to do next?

Helper I

@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.

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors