Forum Discussion
Calculation between date periods
Hi all,
I am really struggling here and could do with some help!!
I am creating a Patching dashboard and as such, i want to show a difference between two date periods
I am showing the count of the following:
Critical
High
Medium
Low
In a nutshell i want to show the amount of criticals at a certain date (which i can change at any given time) and the ammount of critials at another date and then calculate the difference in critical's between the two dates and show this as a percentage and add it to a visual (i was thinking of uding a card)
I am open to some suggestions if anyone has anything they would reccomend.
Hi Anonymous,
I made one sample for your reference.
1. Create a date table and create relationship with the fact table.
Table = CALENDAR(MIN(Table1[date]),MAX('Table1'[date]))2. Create a measure as below.
count = CALCULATE(COUNTROWS('Table1'),ALLSELECTED('Table'))For more details, please check the pbix as attached.
Regards,
Frank
4 Replies
Hi Anonymous,
Please share some sample of your base data and the result you are expecting. The easiest way to share the sample is to copy paste an excel table with some sample values. This helps understanding your issue and requirement.
Regards,
Affan
I am pointing Power BI at an internal folder structure (which i drag and drop my CV's into each day) but below is an example of what one of the CSV's look like (obviously the dates will change per CSV)
plugin_name severity ip_address netbios dns date location MS16-087: Security Update for Windows Print Spooler (3170005) High 00.000.0.00 PC000000 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-118: Cumulative Security Update for Internet Explorer (3192887) High 00.000.0.01 PC000001 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-123: Security Update for Windows Kernel-Mode Drivers (3192892) High 00.000.0.02 PC000002 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-124: Security Update for Windows Registry (3193227) Low 00.000.0.03 PC000003 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-122: Security Update for Microsoft Video Control (3195360) High 00.000.0.04 PC000004 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-120: Security Update for Microsoft Graphics Component (3192884) High 00.000.0.05 PC000005 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-130: Security Update for Microsoft Windows (3199172) High 00.000.0.06 PC000006 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-131: Security Update for Microsoft Video Control (3199151) High 00.000.0.07 PC000007 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-132: Security Update for Microsoft Graphics Component (3199120) High 00.000.0.08 PC000008 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-134: Security Update for Common Log File System Driver (3193706) High 00.000.0.09 PC000009 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-135: Security Update for Windows Kernel-Mode Drivers (3199135) High 00.000.0.10 PC000010 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-137: Security Update for Windows Authentication Methods (3199173) High 00.000.0.11 PC000011 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-138: Security Update for Microsoft Virtual Hard Disk Driver (3199647) High 00.000.0.12 PC000012 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-140: Security Update for Boot Manager (3193479) High 00.000.0.13 PC000013 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-142: Cumulative Security Update for Internet Explorer (3198467) High 00.000.0.14 PC000014 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-144: Cumulative Security Update for Internet Explorer (3204059) High 00.000.0.15 PC000015 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-146: Security Update for Microsoft Graphics Component (3204066) High 00.000.0.16 PC000016 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-147: Security Update for Microsoft Uniscribe (3204063) High 00.000.0.17 PC000017 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-151: Security Update for Windows Kernel-Mode Drivers (3205651) High 00.000.0.18 PC000018 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-153: Security Update for Common Log File System Driver (3207328) Low 00.000.0.19 PC000019 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS16-149: Security Update for Microsoft Windows (3205655) High 00.000.0.20 PC000020 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-009: Security Update for Microsoft Windows PDF Library (4010319) High 00.000.0.21 PC000021 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-011: Security Update for Microsoft Uniscribe (4013076) High 00.000.0.22 PC000022 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-017: Security Update for Windows Kernel (4013081) High 00.000.0.23 PC000023 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-021: Security Update for Windows DirectShow (4010318) Medium 00.000.0.24 PC000024 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-010: Security Update for Microsoft Windows SMB Server (4013389) (ETERNALBLUE) (ETERNALCHAMPION) (ETERNALROMANCE) (ETERNALSYNERGY) (WannaCry) (EternalRocks) (Petya) Critical 00.000.0.25 PC000025 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-018: Security Update for Windows Kernel-Mode Drivers (4013083) High 00.000.0.26 PC000026 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-016: Security Update for Windows IIS (4013074) Medium 00.000.0.27 PC000027 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-022: Security Update for Microsoft XML Core Services (4010321) Medium 00.000.0.28 PC000028 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-012: Security Update for Microsoft Windows (4013078) Critical 00.000.0.29 PC000029 pc000000.corp.local 07/11/2018 PBG - Location - EWC MS17-013: Security Update for Microsoft Graphics Component (4013075) High 00.000.0.30 PC000030 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 April 2017 Security Updates High 00.000.0.31 PC000031 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 May 2017 Security Updates Critical 00.000.0.32 PC000032 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 June 2017 Security Updates Critical 00.000.0.33 PC000033 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 July 2017 Security Updates Critical 00.000.0.34 PC000034 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 August 2017 Security Updates High 00.000.0.35 PC000035 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 September 2017 Security Updates Critical 00.000.0.36 PC000036 pc000000.corp.local 07/11/2018 PBG - Location - EWC KB4038806: Security update for Adobe Flash Player (September 2017) High 00.000.0.37 PC000037 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 October 2017 Security Updates (KRACK) High 00.000.0.38 PC000038 pc000000.corp.local 07/11/2018 PBG - Location - EWC KB4049179: Security update for Adobe Flash Player (October 2017) High 00.000.0.39 PC000039 pc000000.corp.local 07/11/2018 PBG - Location - EWC KB4048951: Security update for Adobe Flash Player (November 2017) High 00.000.0.40 PC000040 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 November 2017 Security Updates High 00.000.0.41 PC000041 pc000000.corp.local 07/11/2018 PBG - Location - EWC Security Updates for Internet Explorer (November 2017) High 00.000.0.42 PC000042 pc000000.corp.local 07/11/2018 PBG - Location - EWC Security Updates for Internet Explorer (October 2017) High 00.000.0.43 PC000043 pc000000.corp.local 07/11/2018 PBG - Location - EWC KB4053577: Security update for Adobe Flash Player (December 2017) Medium 00.000.0.44 PC000044 pc000000.corp.local 07/11/2018 PBG - Location - EWC Windows 8.1 and Windows Server 2012 R2 December 2017 Security Updates High 00.000.0.45 PC000045 pc000000.corp.local 07/11/2018 PBG - Location - EWC Security Updates for Internet Explorer (December 2017) High 00.000.0.46 PC000046 pc000000.corp.local 07/11/2018 PBG - Location - EWC Security Updates for Internet Explorer (January 2018) High 00.000.0.47 PC000047 pc000000.corp.local 07/11/2018 PBG - Location - EWC KB4056898: Windows 8.1 and Windows Server 2012 R2 January 2018 Security Update (Meltdown)(Spectre) High 00.000.0.48 PC000048 pc000000.corp.local 07/11/2018 PBG - Location - EWC
Hi Anonymous,
I made one sample for your reference.
1. Create a date table and create relationship with the fact table.
Table = CALENDAR(MIN(Table1[date]),MAX('Table1'[date]))2. Create a measure as below.
count = CALCULATE(COUNTROWS('Table1'),ALLSELECTED('Table'))For more details, please check the pbix as attached.
Regards,
Frank
Hi Anonymous,
Does that make sense? If so, kindly mark my answer as a solution to close the case.
Regards,
Frank
