cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PowerBeeEye Frequent Visitor
Frequent Visitor

Find percent change in number of servers

Hello I have a table like the one below where information about servers is captured at various times. The table only shows one company, but in reality there are several:

 

partnerforestRoleScanDatenameoperatingsystemoperatingsystemversionCC_OSFamily
ContosoContoso.comProduction6/29/2018 10:43:02 AMSvr01Windows Server 2008 R2 Standard6.1 (7601)Windows Server 2008 R2
ContosoContoso.comProduction6/29/2018 10:43:02 AMSvr02Windows Server 2008 R2 Standard6.1 (7601)Windows Server 2008 R2
ContosoContoso.comProduction6/29/2018 10:43:02 AMSvr03Windows Server 2008 R2 Standard6.1 (7601)Windows Server 2008 R2
ContosoContoso.comProduction11/15/2018 10:43:02 AMSvr01Windows Server 2008 R2 Standard6.1 (7601)Windows Server 2008 R2
ContosoContoso.comProduction11/15/2018 10:43:02 AMSvr02Windows Server 2012 R2 Standard6.3 (9600)Windows Server 2012 R2
ContosoContoso.comProduction11/15/2018 10:43:02 AMSvr03Windows Server 2016 Standard10.0 (14393)Windows Server 2016

 

The last column is calculated:

 

#"Added Conditional Column" = 
	Table.AddColumn(#"Remove_Stale _&_Disabled_Servers", "CC_OSFamily", each if 
Text.StartsWith([operatingsystemversion], "5.0") then "Windows 2000 Server" else if 
Text.StartsWith([operatingsystemversion], "5.2") then "Windows Server 2003" else if 
Text.StartsWith([operatingsystemversion], "6.0") then "Windows Server 2008" else if 
Text.StartsWith([operatingsystemversion], "6.1") then "Windows Server 2008 R2" else if 
Text.StartsWith([operatingsystemversion], "6.2") then "Windows Server 2012" else if 
Text.StartsWith([operatingsystemversion], "6.3") then "Windows Server 2012 R2" else if 
Text.StartsWith([operatingsystemversion], "10.0") then "Windows Server 2016" else null),

 

I then created custom measures for each OS:

 

CM_OSFamily_2008R2 = CALCULATE (DISTINCTCOUNT (ServerReports[dnshostname]),ServerReports[CC_OSFamily]="Windows Server 2008 R2")

 

 

This allowed me to create a graph such as this:

 

1.jpg

 

 

 

 

 

 

 

 

 

 

 

 

Now I would like to also show % change in the number of servers by OS, but can't figure out how.

The one issue with this is that the scan dates are not the same for the different environments, or at regular intervals.

I was thinking more along the lines of this:

For each OS, No. of servers[earliest scan date] / No. of servers [latest scan date] * 100

 

I came as far as figuring out how to find the earliest and latest scan dates:

 

CM_First_Sample = CALCULATE(MIN(ServerReports[ScanDate]),VALUES(ServerReports[forest]))
CM_Last_Sample = CALCULATE(MAX(ServerReports[ScanDate]),VALUES(ServerReports[forest]))

 

But can't get past this.

Any help is greatly appreciated!

2 REPLIES 2
Community Support Team
Community Support Team

Re: Find percent change in number of servers

Hi @PowerBeeEye,

 

Please try below measures. Also, you need to place column [CC_OSFamily] into chart legend.

CM_First_Sample =
CALCULATE (
    MIN ( ServerReports[ScanDate] ),
    VALUES ( ServerReports[forest] ),
    ALLEXCEPT ( ServerReports, ServerReports[CC_OSFamily] )
)


CM_Last_Sample =
CALCULATE (
    MAX ( ServerReports[ScanDate] ),
    VALUES ( ServerReports[forest] ),
    ALLEXCEPT ( ServerReports, ServerReports[CC_OSFamily] )
)

CM_OSFamily earliest =
CALCULATE (
    DISTINCTCOUNT ( ServerReports[name] ),
    FILTER (
        ALLEXCEPT ( ServerReports, ServerReports[forest], ServerReports[CC_OSFamily] ),
        ServerReports[ScanDate] = [CM_First_Sample]
    )
)

CM_OSFamily latest =
CALCULATE (
    DISTINCTCOUNT ( ServerReports[name] ),
    FILTER (
        ALLEXCEPT ( ServerReports, ServerReports[CC_OSFamily], ServerReports[forest] ),
        ServerReports[ScanDate] = ServerReports[CM_Last_Sample]
    )
)

% change = [CM_OSFamily earliest]/[CM_OSFamily latest]

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
PowerBeeEye Frequent Visitor
Frequent Visitor

Re: Find percent change in number of servers

PBI.jpg

 

Thank you, but I don't think its working; I have a few follow up questions:

1. What is the best visializer for this? I used Area chart, but is another better?

2. I can't add CC_OSFamily to the chart legend - no error, it just does not add.

3. What measures should I add to the Values?