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. ServerJobNameStartDateStartTimeEndDateEndTimeDR-SIM-10331020349.bat3/31/20223:55:25 AM3/31/202211:52:08 AMDR-SIM-10331203641.bat3/31/20228:37:25 PM4/1/20225:49:20 AMDR-SIM-10331212914.bat4/1/20225:49:43 AM4/1/202210:57:35 AMDR-SIM-10401005228.bat4/1/202210:57:39 AM4/1/202211:02:57 AMDR-SIM-10401133657.bat4/1/20221:37:39 PM4/1/202210:11:14 PMDR-SIM-10401152430.bat4/1/202210:11:26 PM4/2/202211:24:48 AMDR-SIM-10401154749.bat4/2/202211:25:03 AM4/3/202212:49:29 AMDR-SIM-10404193450.bat4/4/20227:35:44 PM4/6/202211:55:44 PMDR-SIM-10407021825.bat4/7/20222:18:49 AM4/7/20221:30:57 PMDR-SIM-10408220137.bat4/8/202210:02:23 PM4/9/20222:48:14 AMDR-SIM-10410011850.bat4/10/20221:19:20 AM4/10/20228:11:11 AMDR-SIM-10410102920.bat4/10/202210:30:00 AM4/11/20224:58:53 AMDR-SIM-10411050032.bat4/11/20225:02:53 AM4/11/20225:17:13 AMDR-SIM-10411060848.bat4/11/20226:09:07 AM4/11/20226:10:45 AMDR-SIM-10411080023.bat4/11/20228:00:47 AM4/11/20222:26:15 PMDR-SIM-10411211617.bat4/11/20229:16:58 PM4/11/20229:24:31 PMDR-SIM-10411212513.bat4/11/20229:25:36 PM4/12/20222:15:01 AMDR-SIM-10412024754.bat4/12/20222:48:15 AM4/12/20227:00:43 AMDR-SIM-10412070926.bat4/12/20227:09:41 AM4/12/20227:32:30 AMDR-SIM-10412075021.bat4/12/20227:50:43 AM4/12/20228:01:40 AMDR-SIM-10412151749.bat4/12/20223:18:26 PM4/12/20224:24:46 PMDR-SIM-10412164643.bat4/12/20224:47:09 PM4/12/20224:57:35 PMDR-SIM-10413160506.bat4/13/20224:06:12 PM4/13/20224:58:36 PMDR-SIM-10413215225.bat4/13/20229:53:18 PM4/14/20225:12:22 AMDR-SIM-10414054626.bat4/14/20225:46:44 AM4/14/20226:16:35 AMDR-SIM-10414145407.bat4/14/20222:55:15 PM4/14/20223:01:20 PMDR-SIM-10415065640.bat4/15/20226:57:05 AM4/15/20227:10:41 AMDR-SIM-10415072431.bat4/15/20227:24:54 AM4/15/20227:37:30 AMDR-SIM-10415073901.bat4/15/20227:39:22 AM4/15/20227:53:15 AMDR-SIM-10415081515.bat4/15/20228:15:38 AM4/15/20228:28:55 AMDR-SIM-10415082010.bat4/15/20228:29:07 AM4/15/20228:42:49 AMDR-SIM-10415082649.bat4/15/20228:43:02 AM4/15/20228:54:23 AMDR-SIM-10415082800.bat4/15/20228:54:26 AM4/15/20229:10:42 AMDR-SIM-10415160807.bat4/15/20224:08:57 PM4/18/20221:27:08 PM I know it is possible, but I don't even know the right questions to ask to get me closer. Thanks

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.

Calculate Server Usage | Microsoft Fabric Community

Forum Discussion

Related content

Usage Reports

Inquiry on PBI Report Server Usage and Licensing

Copilot Agent Usage Report

Usage Metric report Refresh Issue

Report usage metrics