How to calculate a running total of Vulnerabilities?

[erhine1204]

Hello,

For the life of me I cannot figure out how to create a running total trend line of vulnerabilities from my organizations Fortify SAST tool. Below is an example of the data that I am currently exporting into Excel. What I am trying to do is show a running total of vulnerabilities by Severity at an enterprise level for all applications, and then at an individual application level. I would also create trend lines for OWASP status, Developer Status, etc.

Example Data

Vuln ID	Severity	Status	Developer Status	Introduced Date	OWASP	ApplicationID	ReleaseID	Category
1	Critical	Fix Validated	Will Not Fix	1/2/2018	A1	12345	21212	Injection
2	Critical	New	Will Not Fix	2/6/2019	A2	12345	21212	Session Mgmt
3	Critical	Existing	Open	2/12/2019	A2	32165	321321	Session Mgmt
4	Critical	Existing	Open	5/8/2020	A3	32165	654654	Privacy Violation
5	High	New	Open	12/1/2020	A9	11111	654654	3rd party
6	Medium	Re-Opened	In Progress	1/16/2021	A9	11111	212121	3rd party
7	Low	Existing	In Progress	2/3/2021	A1	12345	321321	Injection

I have been able to create the trend line data I am looking for with Excel formulas by running a count of every status every week. Here is an example of the data and trend that I am trying to reproduce in Power BI

Any help on this would be greatly appreciated.

Thank you,

Eric

[dedelman_clng]

Hi @erhine1204  - 

Try this code for your running count:

Running Total =
CALCULATE (
    COUNTROWS ( Vulnerabilities ),
    ALLSELECTED ( Vulnerabilities ),
    FILTER ( ALLSELECTED ( DateTab[Date] ), DateTab[Date] <= MAX ( DateTab[Date] ) )
)

This assumes you have a Calendar table linked to Vulnerabilities based on "Intorduced Date".

If this doesn't work, please share DAX that you have tried and/or a copy of your PBIX with sensitive data removed (link to google drive, OneDrive, dropbox, etc)

Hope this helps

David