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
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.

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 ()
    )