Forum Discussion

putriwid's avatar
putriwid
Frequent Visitor
6 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
6 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

putriwid's avatar
putriwid
Frequent Visitor
6 years ago

hello v-diye-msft ,

 

  •  i have try using the M code that u gave in power query and it looks like this:

once i click the 'table' on the custom column, it looks exactly like the one that u share.

However, it doesnt show the data from other date.

 

  • Fyi, M code is very unfamiliar for me. Do you have any simpler measurement? 

thank you in advance.

  • v-diye-msft's avatar
    v-diye-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi putriwid 

     

    let me specify more detailed steps of the power query:

    1. Add the index column from 1 to add the first index column:

    2. Inserted the division using divide by 2

    3. Round up the values

    4. Delete the Division column and change the "Round up" column as "Index 2"

     

     

    5. Then try the measure I provided before

     

     

     

    If you can't fix it, Please kindly share your dummy pbix here (Upload it to dropbox/onedrive and generate the link), that I can draw it up for you. 

     

     

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    6 years ago

    putriwid ,

    you can use this to created a calculated column of durations:

    StartTime =
VAR currentLineDateTime =
    CALCULATE ( MIN ( 'Table'[date] ) )
VAR currentLineStatus =
    CALCULATE ( MIN ( 'Table'[dstatus] ) )
RETURN
    IF (
        currentLineStatus = 12;
        DATEDIFF (
            CALCULATE (
                MAX ( 'Table'[date] );
                FILTER (
                    ALL ( 'Table' );
                    'Table'[date] < currentLineDateTime
                        && 'Table'[dstatus] = 1
                )
            );
            currentLineDateTime;
            SECOND
        );
        BLANK ()
    )