New Incidents from a week over week

DAX DAX Calculations
1

Hi,
I get a new data set each with a list of vulnerabilities. I want to show how many vulnerabilities are in this week that were not in last week. The goal of the team is to patch all vulnerabilities, so if a vulnerability appears week over week then its’ not getting patched. As well if a vulnerability appears in the latest report but was not in the prior week then it’s considered a “new” vulnerability and i want to see how many new ones appear week over week.

A single vulnerability can be on multiple servers. To calculate the total vulnerabilities I created a new merged column between the (The Date - Device Name - Vul Number QID).
EDNA - Vulnerabilities.pbix (18.1 KB)

EDNA - Trending New Incidents.xlsx (17.1 KB)

2

I didn’t see an edit button so i need to reply to my own post for a correction… My logic of merging the columns to get a unique vulnerability is not correct. Now that i think about it, once i merge the date it’s then going to look across each week and say everything is unique because of the date.

I should have just merged the Vulnerabiltiy QUI number and the device name. Then if that merged dat point is not there the following week, it would mean the vulnerability on the device was removed.

3

@chad.sharpe ,

As with most week over week calculations, I think this is best handled via week offsets in the Extended Date Table.

Here’s the measure that does the heavy lifting:

Unique Incident Count = 
VAR MaxWkOffset = [Max Week Offset]
VAR IncidentsMaxWeek =
    CALCULATETABLE(
        DISTINCT( 'Table'[Incident Key] ),
        Dates[WeekOffset] = MaxWkOffset
    )
VAR IncidentsWeekPrior =
    CALCULATETABLE(
        DISTINCT( 'Table'[Incident Key] ),
        Dates[WeekOffset] = MaxWkOffset - 1
    )
VAR Result =
    COUNTROWS( EXCEPT( IncidentsMaxWeek, IncidentsWeekPrior ) )
RETURN
    Result

image
image890×233 7.61 KB

I hope this is helpful. Full solution file attached below.

– Brian

eDNA Forum - Count Unique Incidents WoW Solution.pbix (83.0 KB)

1 Like
4

I think I may not have explained it well enough. I’ll play with these measures and see if I can get closer to my goal. I’ll draw up a bar chart showing what I am trying to get to as well.

5

@chad.sharpe ,

Sorry, my bad. I banged this out in between Summit sessions, and was so tired I barely remember doing it. I read it over again tonight, and I think this is more what you’re looking for:

image
image2285×1270 168 KB 


Unique Incident Count = 

VAR SelWkOff = [Selected Week Offset]

VAR IncidentsMaxWeek =
    CALCULATETABLE(
        DISTINCT( 'Table'[Incident Key] ),
        ALL(Dates),
        Dates[WeekOffset] = SelWkOff
    )
VAR IncidentsWeekPrior =
    CALCULATETABLE(
        DISTINCT( 'Table'[Incident Key] ),
        ALL(Dates ),
        Dates[WeekOffset] = SelWkOff - 1
    )
VAR Result =
    COUNTROWS( EXCEPT( IncidentsMaxWeek, IncidentsWeekPrior ) )
RETURN
    Result

Please let me know if this hits the mark.

– Brian

eDNA Forum - Count Unique Incidents WoW Solution2.pbix (84.5 KB)

1 Like
6

Ah… yes this looks good… GREAT!!

How do you make a guy feel like he has no idea what he’s doing… say “I kicked this out in between sessions”… lololol

7

@chad.sharpe ,

LOL. I didn’t mean it that way. :grinning:

Just turns out I’ve been doing a lot of Week over Week DAX lately (have actually done a few YT videos on this as a result of some cool stuff I’ve learned). In addition, my favorite type of DAX problems for some reason are those that involve virtual table manipulation, like this one does. So, while not a simple question, it just happened to be a pitch right over the plate at exactly the right location and speed that I like…

Glad this got you what you needed.

  • Brian
2 Likes