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 cou...
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,
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 |