Forum Discussion

putriwid's avatar
putriwid
Frequent Visitor
7 years ago

How to find datediff in the same column with condition

Hello guys, I have a table that consists of 6 columns. the table looks like this: i want to create the measurement on how long the duration when the 'dtstatus'= 1 until the 'dtstatus' changed...
Need Help
Show and Tell
Tutorial Requests
v-diye-msft's avatar
v-diye-msft
Icon for Community Support rankCommunity Support
7 years ago

Hi putriwid 

 

Let me know if you'd like to get below results:

1. I created a simple sample as your data:

2. Add below M code to add 2 index columns:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bctBCsAgDATAr5ScBZONrWa/Iv7/GwZ6avE8zJxiUgRqUU0r4jLwDqrJKmn4IdXp/bUvgTbocWxpoI9Ta8TD1o+t5aGmrQ0=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [dtstatus = _t, Time = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"dtstatus", Int64.Type}, {"Time", type datetime}}),
    #"Added Index" = Table.AddIndexColumn(#"Changed Type", "Index", 1, 1),
    #"Inserted Division" = Table.AddColumn(#"Added Index", "Division", each [Index] / 2, type number),
    #"Inserted Round Up" = Table.AddColumn(#"Inserted Division", "Round Up", each Number.RoundUp([Division]), Int64.Type),
    #"Removed Columns" = Table.RemoveColumns(#"Inserted Round Up",{"Division"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Round Up", "Index2"}})
in
    #"Renamed Columns"

3. Then add below measure:

Measure 2 = var lastindex = MAX('Table (2)'[Index])-1
var lasttime = CALCULATE(MAX('Table (2)'[Time]),FILTER(ALL('Table (2)'),[Index]=lastindex),VALUES('Table (2)'[Index2]))
var timediff = DATEDIFF(lasttime,MAX('Table (2)'[Time]),SECOND)
Return 
timediff