Calculate Server Usage

[jb_graphic]

I want to be able to calculate how many hours a day a server has Jobs running and when it is not running Jobs.
My goal is to be able to create a visual in Power BI that can show on any given day if it was active and for how long.
This is sample of the data I have to work with, I am very new to power bi but am very willing to learn.

As you can see multiple jobs can run per day. And some Jobs run over serval days so I need to be able to split the run time up into the revelant days.
i.e a Job may actually run for 2 hours one day and 24 for the next two days and 20min the last day.
I need to be able to capture that information so I can display.

Server	JobName	StartDate	StartTime	EndDate	EndTime
DR-SIM-1	0331020349.bat	3/31/2022	3:55:25 AM	3/31/2022	11:52:08 AM
DR-SIM-1	0331203641.bat	3/31/2022	8:37:25 PM	4/1/2022	5:49:20 AM
DR-SIM-1	0331212914.bat	4/1/2022	5:49:43 AM	4/1/2022	10:57:35 AM
DR-SIM-1	0401005228.bat	4/1/2022	10:57:39 AM	4/1/2022	11:02:57 AM
DR-SIM-1	0401133657.bat	4/1/2022	1:37:39 PM	4/1/2022	10:11:14 PM
DR-SIM-1	0401152430.bat	4/1/2022	10:11:26 PM	4/2/2022	11:24:48 AM
DR-SIM-1	0401154749.bat	4/2/2022	11:25:03 AM	4/3/2022	12:49:29 AM
DR-SIM-1	0404193450.bat	4/4/2022	7:35:44 PM	4/6/2022	11:55:44 PM
DR-SIM-1	0407021825.bat	4/7/2022	2:18:49 AM	4/7/2022	1:30:57 PM
DR-SIM-1	0408220137.bat	4/8/2022	10:02:23 PM	4/9/2022	2:48:14 AM
DR-SIM-1	0410011850.bat	4/10/2022	1:19:20 AM	4/10/2022	8:11:11 AM
DR-SIM-1	0410102920.bat	4/10/2022	10:30:00 AM	4/11/2022	4:58:53 AM
DR-SIM-1	0411050032.bat	4/11/2022	5:02:53 AM	4/11/2022	5:17:13 AM
DR-SIM-1	0411060848.bat	4/11/2022	6:09:07 AM	4/11/2022	6:10:45 AM
DR-SIM-1	0411080023.bat	4/11/2022	8:00:47 AM	4/11/2022	2:26:15 PM
DR-SIM-1	0411211617.bat	4/11/2022	9:16:58 PM	4/11/2022	9:24:31 PM
DR-SIM-1	0411212513.bat	4/11/2022	9:25:36 PM	4/12/2022	2:15:01 AM
DR-SIM-1	0412024754.bat	4/12/2022	2:48:15 AM	4/12/2022	7:00:43 AM
DR-SIM-1	0412070926.bat	4/12/2022	7:09:41 AM	4/12/2022	7:32:30 AM
DR-SIM-1	0412075021.bat	4/12/2022	7:50:43 AM	4/12/2022	8:01:40 AM
DR-SIM-1	0412151749.bat	4/12/2022	3:18:26 PM	4/12/2022	4:24:46 PM
DR-SIM-1	0412164643.bat	4/12/2022	4:47:09 PM	4/12/2022	4:57:35 PM
DR-SIM-1	0413160506.bat	4/13/2022	4:06:12 PM	4/13/2022	4:58:36 PM
DR-SIM-1	0413215225.bat	4/13/2022	9:53:18 PM	4/14/2022	5:12:22 AM
DR-SIM-1	0414054626.bat	4/14/2022	5:46:44 AM	4/14/2022	6:16:35 AM
DR-SIM-1	0414145407.bat	4/14/2022	2:55:15 PM	4/14/2022	3:01:20 PM
DR-SIM-1	0415065640.bat	4/15/2022	6:57:05 AM	4/15/2022	7:10:41 AM
DR-SIM-1	0415072431.bat	4/15/2022	7:24:54 AM	4/15/2022	7:37:30 AM
DR-SIM-1	0415073901.bat	4/15/2022	7:39:22 AM	4/15/2022	7:53:15 AM
DR-SIM-1	0415081515.bat	4/15/2022	8:15:38 AM	4/15/2022	8:28:55 AM
DR-SIM-1	0415082010.bat	4/15/2022	8:29:07 AM	4/15/2022	8:42:49 AM
DR-SIM-1	0415082649.bat	4/15/2022	8:43:02 AM	4/15/2022	8:54:23 AM
DR-SIM-1	0415082800.bat	4/15/2022	8:54:26 AM	4/15/2022	9:10:42 AM
DR-SIM-1	0415160807.bat	4/15/2022	4:08:57 PM	4/18/2022	1:27:08 PM

I know it is possible, but I don't even know the right questions to ask to get me closer.

Thanks

[Anonymous · Accepted solution]

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. 

[jb_graphic]

This is exactly what I am looking for.

The one thing I would like to add is that when there is no activity on a day it logs it as 0 activity or 24 downtime, which ever is easier I would say.

[jb_graphic]

It would look something like this. the times are not accurate in this example

Date             Active (hrs)Down Time (hrs)

3/31/2022	10.00	14.00
4/1/2022	24.00	0.00
4/2/2022	1.00	23.00
4/3/2022	1.00	23.00
4/4/2022	18.00	6.00
4/5/2022	24.00	0.00
4/6/2022	24.00	0.00

[sevenhills]

Can you provide the expected output?