Binary Runtime/Downtime Visualization

[_rocklicker_]

I have a data set similar to the table below with a machine ID, and columns for start and end of downtime periods. Machines may or may not have multiple records for downtime intervals. I'd like to be able to have a binary visual similar to the below paint mockup showing date along x axis and the status of the machine as either "up/running" or "down" . I figure a calendar table is in order but struggling with how to transform/join the data to get the desired result for visualization - essentially if a given date is between a start/end date for a given machine it's a 0 (down) and if it's not it's a 1 (up) for that machine.

Tried searching the forums but haven't found quite the result I'm looking for, please let me know if I missed a solution (first time posting on here)

MachineIDDowntimeStartDowntimeEnd

1	1-May	1-Jul
1	1-Jan	1-Feb
2	1-Jan	1-Mar
2	1-Sep	5-Sep
3	1-Apr	4-Apr
4	8-May	31-May
4	1-Mar	5-Mar

[Vera_33 · Accepted solution]

Hi @_rocklicker_ 

Here is one way, Date from a connected Date Table, test measure as below

test = 
VAR CurDate = MAX('Date'[Date])
VAR T1 = SELECTCOLUMNS(GENERATE('Table',DATESBETWEEN('Date'[Date],'Table'[DowntimeStart],'Table'[DowntimeEnd])),"Dates",[Date])
RETURN
IF(CurDate in T1, 0, 1)

[v-deddai1-msft · Accepted solution]

Hi @_rocklicker_ ,

You can use power query to expand all dates from start date and end date:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8k1MzsjMS/V0UdJRcskvzyvJzE0NLkksKkHiu+alKMXqRCsZAsUMdX0TK8G0V2kOkqhXYh6YdktNAosaoYj6JhYhiQanFgBpUzANEjUGizoWFAFpEzANEjUB8iygthlDrIUJQwwEGQE2OBYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"MachineID", Int64.Type}, {"DowntimeStart", type date}, {"DowntimeEnd", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each {Number.From([DowntimeStart])..Number.From([DowntimeEnd])}),
    #"Expanded Custom" = Table.ExpandListColumn(#"Added Custom", "Custom"),
    #"Changed Type2" = Table.TransformColumnTypes(#"Expanded Custom",{{"Custom", type date}})
in
    #"Changed Type2"

Then use the following measure:

Measure = IF(MAX('Date'[Date]) IN VALUES('Table'[Custom]),0,1)

For more details, please refer to the pbix file. 

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

Best Regards,

Dedmon Dai

[Vera_33 · Accepted solution]

Hi @_rocklicker_ 

Here is one way, Date from a connected Date Table, test measure as below

test = 
VAR CurDate = MAX('Date'[Date])
VAR T1 = SELECTCOLUMNS(GENERATE('Table',DATESBETWEEN('Date'[Date],'Table'[DowntimeStart],'Table'[DowntimeEnd])),"Dates",[Date])
RETURN
IF(CurDate in T1, 0, 1)