Binary Runtime/Downtime Visualization

Hi Anonymous 

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)

Hi Anonymous ,

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