Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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.
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.
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...
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 |
Based on your sample data I arrived at this step:
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:
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
21 | |
21 | |
19 | |
13 | |
12 |
User | Count |
---|---|
41 | |
29 | |
23 | |
22 | |
22 |