Calculate Server Usage

Hi jb_graphic ,

 

Click "transform data" to enter the power query editor, open "Advanced Editor" and copy and paste the following code.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("dZbLThxBDEV/JZo1BD/r4R0SmyyQomSJWCTfkP9X7Ga6q6bbJTZojM/Yvr4uPj5ub7+ef/94f8bb0w2YEQhY+ve/f/75B/zC+EJAFL+bqpF+e30/BRBNyaBF5PPpAnReEUyAzbgG8GcA5eX4XE26EaxwSB3ljjtnCX+VN32OYFqNNcMJIIAStSvuntavPDQgjy14yFy0Jrxo1nHnZv17nIgSgQynJAxpeZ5GZefRVB6JSSrGxpN6qHtKU4NjfHwEaFOj5zzBzqKjPtnTYuImspdX5l3ZA1dcBcJGeuDqnkaGzevYq6vTVEOnnNaIAHlo0abhuYTEe3V9fI200CLr1TcFsU29IowqcF/Yx0DbtMUFz63WKeVBtAUDeMgups2UcyCCAjAN4OSNWFm+8tSwGi55BZq0hFcMukG98op57ZJ7zXkNgDjhNW/WJOG5So7UVF/0U4AFa8LrhsUndZhtCrg5GJc8Uszq62EOPsyGNO2lz3YhsP+NVJ1OFT3umR790rBNDGKhB7k/OpWEV0MPwYzH5Lu05KkbLuUpzLeUJqHQZMVDxfm4jDQO+1IyP9mOVcn1ICxShBOe50TLGe/r1qc8xuIOmebHIw18zejg8YPfeFEfe8M0XauR1t1s3vLBk8lvvtKUz09Apcz6TmlS4mi+nnklFn3xtqH/qN/UhEdxhFGv9XHo65cs7ddnp0Wmg6WjDB87jH3WsUhxDxb+UKj+uGHCq7EWKhkvXtHF/jmPO+Q87vexnwMh1GJ+Cs03WhNemNe4XXm+5X6f1zx/kLL5edrDPZ0CQvd3L+eV2W9zmiuZ9OvFSTx8S55f6JQXaeXK65u+i31W91ub908nv7X7s70FxstsVOM/ydi/z/8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Server = _t, JobName = _t, StartDate = _t, StartTime = _t, EndDate = _t, EndTime = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Server", type text}, {"JobName", type text}, {"StartDate", type date}, {"StartTime", type time}, {"EndDate", type date}, {"EndTime", type time}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each List.Numbers(1,Duration.Days([EndDate]-[StartDate])+1)),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom", "Dates", each Date.AddDays([StartDate],[Custom]-1)),
    #"Inserted Merged Column" = Table.AddColumn(#"Added Custom2", "Merged", each Text.Combine({Text.From([StartDate], "en-US"), Text.From([StartTime], "en-US")}, " "), type text),
    #"Changed Type1" = Table.TransformColumnTypes(#"Inserted Merged Column",{{"Merged", type datetime}}),
    #"Inserted Merged Column1" = Table.AddColumn(#"Changed Type1", "Merged.1", each Text.Combine({Text.From([EndDate], "en-US"), Text.From([EndTime], "en-US")}, " "), type text),
    #"Changed Type2" = Table.TransformColumnTypes(#"Inserted Merged Column1",{{"Merged.1", type datetime}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type2", "Active (hrs)", each let 
a=Table.RowCount(Table.SelectRows(
    #"Changed Type2",(x)=>x[JobName]=[JobName])),
b=List.Max(Table.SelectRows(#"Changed Type2",(x)=>x[JobName]=[JobName])[Custom]),
c=DateTime.FromText(Text.From(Date.AddDays([StartDate],1))&" "&Text.From(Time.FromText("12:00:00 AM"))),
d=DateTime.FromText(Text.From([StartDate])&" "&Text.From(Time.FromText("12:00:00 AM")))
in 
if a=1 then Number.Round((Duration.Hours([Merged.1]-[Merged])*3600+
Duration.Minutes([Merged.1]-[Merged])*60+Duration.Seconds([Merged.1]-[Merged]))/3600,2) else if [Custom]=1 then Number.Round((Duration.Hours(c-[Merged])*3600+
Duration.Minutes(c-[Merged])*60+Duration.Seconds(c-[Merged]))/3600,2) else if [Custom]=b then Number.Round((Duration.Hours([Merged.1]-d)*3600+
Duration.Minutes([Merged.1]-d)*60+Duration.Seconds([Merged.1]-d))/3600,2) else 24),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom1",{"Custom", "Merged", "Merged.1"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Removed Columns",{{"Dates", type date}})
in
    #"Changed Type3"

Then we can create calculated columns.

_Active (hrs) = CALCULATE(SUM('Table'[Active (hrs)]),FILTER('Table','Table'[Dates]=EARLIER('Table'[Dates])))

_Down Time (hrs) = 24-[_Active (hrs)]

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly.