Need to Add a Condition Test to Adjust Count of Days Between Entries

[jvansickler]

We have the following two-element query that enumerates the number of days between scans.  We need to add a third element to reset the count to (Blank), indicating that a remediated vulnerability has been detected again on a host, and the time-taken-to-remediate clock has been reset.

The current query:

ScanGap = 
VAR temp =
    TOPN (
        1,
        FILTER (
            FactSourceCSVs,
			FactSourceCSVs[CVE-HostKey] = EARLIER ( FactSourceCSVs[CVE-HostKey] )
				&& FactSourceCSVs[Scan Date] < EARLIER ( FactSourceCSVs[Scan Date] )
            ),
        [Scan Date], DESC
    )
RETURN
    DATEDIFF ( MINX ( temp, [Scan Date] ), FactSourceCSVs[Scan Date], DAY )

Returns the following result:

ScanDate	CVEHostKey	ScanGap	EndOcc
4/24/2024	cve-2024-21012-server01		0
4/26/2024	cve-2024-21012-server01	2	0
4/29/2024	cve-2024-21012-server01	3	0
4/30/2024	cve-2024-21012-server01	1	0
5/8/2024	cve-2024-21012-server01	8	0
5/13/2024	cve-2024-21012-server01	5	1
10/31/2024	cve-2024-21012-server01	171	0
11/1/2024	cve-2024-21012-server01	1	0
11/4/2024	cve-2024-21012-server01	3	0
11/7/2024	cve-2024-21012-server01	3	0
11/11/2024	cve-2024-21012-server01	4	0

If the End of Occurrence (EndOcc) column contains a "1", the next ScanDate should be Blank.  In the example above, the CVE was detected on 4/24/2024, and remediated between the 5/13/2024 scan and the one that followed it.  The 10/31/2024 scan picked it up again, so it should be blank, not the count of days between 5/13 and 10/24.  If the CVE is remediated after the 11/11/2024 scan, the next EndOcc will change to 1 and the CVE will stop showing up in the scans.  Until the next time, if there is one.

Any help/guidance would be appreciated.

[jvansickler]

Worked perfectly - thanks!

[lbendlin]

Please indicate the expected outcome based on the sample data you provided.  Note that most likely this will require a Power Query solution.

[jvansickler]

The data that I'm working from has about 250k rows right now. EndOcc only contains 0 or 1.  The ScanGap coumn does not exist in the data; it was created in the visualization, and uses the query above to derive the number of days between the scan dates for a particular CVE.  The EndOcc value (0 or 1) needs to be taken into consideration as well.  Most of the existing ScanGap values will remain the same, but the first ScanDate for a new instance of that CVE should be set to a Blank value to indicate it's not a continuation of the earlier instance.  In the example above, the first iteration ends on 5/13/2024, and the second iteration begins on 10/31/2024, and should be Blank, not 171 days.  This will apply to all CVE/Host entries that are displayed using the slicer values.

Hopefully that helps clarify what I'm trying to do, and the updated table provides a visual representation of the desired result.

Update:

If I change the last line to the following, it sets the 5/13/2024 entry to BLANK.  I need to use DATEADD or something similar to move to the next calendar entry (10/31/2024) and BLANK that cell instead.

IF ( FactSourceCSVs[EndOcc] = 1, (DATEDIFF ( MINX ( temp, [Scan Date] ), FactSourceCSVs[Scan Date], DAY )), BLANK() )

I need to keep alll of the other ScanGap values, and only blank out the ones that follow an EndOcc=1

[Anonymous · Accepted solution]

Hi All,
Firstly  lbendlin thank you for your solution!
And @jvansickler ,According to your requirement, you want the Occ column to show 1, his next ScanDate to be blank, and then to calculate the date of the two ScanDates, right?
I'll try to improve your code according to your needs, hope it will help you.

ScanGap = 
VAR temp =
    TOPN(
        1,
        FILTER(
            FactSourceCSVs,
            FactSourceCSVs[CVEHostKey] = EARLIER(FactSourceCSVs[CVEHostKey])
                && FactSourceCSVs[ScanDate] < EARLIER(FactSourceCSVs[ScanDate])
        ),
        [ScanDate], DESC
    )
VAR prevDate = MINX(temp, [ScanDate])
VAR daysBetween =
    COALESCE(DATEDIFF(prevDate, FactSourceCSVs[ScanDate], DAY),0)
VAR endOccCheck =
    CALCULATE(
        MAX(FactSourceCSVs[EndOcc]),
        FILTER(
            FactSourceCSVs,
            FactSourceCSVs[CVEHostKey] = EARLIER(FactSourceCSVs[CVEHostKey])
                && FactSourceCSVs[ScanDate] = prevDate
        )
    )
RETURN
IF(endOccCheck = 1, BLANK(), daysBetween)

If you still have questions, check out the pbix file I uploaded and I'd be honored if I could solve your problem!

Hope it helps!

Best regards,
Community Support Team_ Tom Shen

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

[jvansickler]

The expected result would change "171" to a blank cell,  e.g.:

ScanDate	CVEHostKey	ScanGap	EndOcc
4/24/2024	cve-2024-21012-server01		0
4/26/2024	cve-2024-21012-server01	2	0
4/29/2024	cve-2024-21012-server01	3	0
4/30/2024	cve-2024-21012-server01	1	0
5/8/2024	cve-2024-21012-server01	8	0
5/13/2024	cve-2024-21012-server01	5	1
10/31/2024	cve-2024-21012-server01		0
11/1/2024	cve-2024-21012-server01	1	0
11/4/2024	cve-2024-21012-server01	3	0
11/7/2024	cve-2024-21012-server01	3	0
11/11/2024	cve-2024-21012-server01	4	0