Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Calculating Intervals Between Separate Rows of Data

Hi everyone! Really new to PowerBI (when I say new, I just started fiddling with it a week ago) and I'm having a problem working on this with DAX. I found a similar thread but it doesn't deal with t...
  • lbendlin's avatar
    4 years ago

    Does it have to be DAX?  This can be solved quicker in Power Query.

     

     

    let
        Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdAxCsMwDAXQqxjPgVhfVtr6FNlNxq4ttPeHOKoFcYjTzV/o8YVz9uT84Ofn5/t+OX07GWkEHEkK4TDgMliG7NFDCInCYWCIL1BUZJlKs5K4I/hDoiiRbgvpJQ35tUzdFkrSENhhtx3hhqD+mmU2cj8h2FZ0YwOaxPYfvX1JqA01cgHLCg==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, Person = _t, START = _t, END = _t]),
        #"Changed Type" = Table.TransformColumnTypes(Source,{{"ID", Int64.Type}, {"START", type datetime}, {"END", type datetime}}),
        #"Grouped Rows" = Table.Group(#"Changed Type", {"Person"}, {{"Rows", each _, type table [Index=nullable number, Person=nullable text, START=nullable datetime, END=nullable datetime]}}),
        idx = (tbl)=>Table.AddIndexColumn(tbl, "Index", 0, 1, Int64.Type),
        #"Added Index" = Table.AddColumn(#"Grouped Rows","Rows2",each idx([Rows])),
        diff = (tbl)=> Table.AddColumn(tbl, "Difference", each if [Index]=0 then null else if [START]- tbl{[Index]-1}[END] <= #duration(0,4,0,0) then [START]- tbl{[Index]-1}[END] else null),
        #"Added Custom" = Table.AddColumn(#"Added Index","Rows3",each diff([Rows2])),
        #"Removed Other Columns" = Table.SelectColumns(#"Added Custom",{"Rows3"}),
        #"Expanded Rows3" = Table.ExpandTableColumn(#"Removed Other Columns", "Rows3", {"ID", "Person", "START", "END","Difference"}, {"ID", "Person", "START", "END", "Difference"})
    
    in
        #"Expanded Rows3"

     

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".