Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Sonnet
Helper I
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] > 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:

Sonnet_0-1708968437042.png

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
lbendlin
Super User
Super User

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

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

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

 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.

 

Sonnet_0-1709054839707.png

 

 

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

Thanks for your help.
Please consider the below sample data table to work a solution.

 

 

IDNameOpening DateCompletion DateTask Type   
PT00001Name 0000124/08/202301/02/2024Project   
PT00002Name 0000211/09/202328/09/2023Task Project180 days
PT00003Name 0000322/09/202324/12/2023Assignment Task14 days
PT00004Name 0000413/10/202323/10/2023Support Ticket Assignment90 days
PT00005Name 0000511/09/202309/01/2024Project Support Ticket 3 days
PT00006Name 0000606/10/202318/10/2024Task   
PT00007Name 0000719/10/202302/02/2024Assignment   
PT00008Name 0000816/11/202320/11/2023Support Ticket   
PT00009Name 0000917/11/2023 Project   
PT00010Name 0001020/11/202321/11/2023Support Ticket   
PT00011Name 0001121/11/202301/12/2023Support Ticket   
PT00012Name 0001222/11/202322/11/2023Support Ticket   
PT00013Name 0001309/12/2023 Project   
PT00014Name 0001424/12/202309/01/2024Task   
PT00015Name 0001518/01/2024 Assignment   
PT00016Name 0001620/01/202428/01/2024Support Ticket   
PT00017Name 0001710/01/202420/01/2024Support Ticket   
PT00018Name 0001831/01/202411/04/2024Task   
PT00019Name 0001922/02/2024 Assignment 

Based on your sample data I arrived at this step:

lbendlin_0-1709083560442.png

What do you want to do next?

 

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

PT00007Name 0000719/10/202302/02/2024Assignment

 

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.

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

 

lbendlin_0-1709166893768.png

 

 

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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

Top Solution Authors