Forum Discussion

RhodesBrown's avatar
RhodesBrown
Frequent Visitor
3 years ago
Solved

Time in Status

Hi All   Essentially I'm trying to add a new column that measures how long in hrs and minutes each row spent in that status .  For instance how long did it take row 2 to go to row 1 (Relationshi...
Need Help
  • ahmedoye's avatar
    ahmedoye
    3 years ago

    Try the below: (Just change references to "Table" to the original name of your table. If it works, kindly mark it as a solution so anyone who needs a similar solution in future can easily find it.

     

    Column =
    VAR CurrentCreatedon = 'Table'[createdon]
    VAR CurrentName = 'Table'[Name]
    VAR NextTime =
        MINX (
            TOPN (
                1,
                FILTER (
                    'Table',
                    'Table'[createdon] > CurrentCreatedon
                        && 'Table'[Name] = CurrentName
                ),
                'Table'[createdon], ASC
            ),
            'Table'[createdon]
        )
    VAR TimeDifferenceM =
        DATEDIFF ( CurrentCreatedon, NextTime, MINUTE )
    VAR TimeDifferenceS =
        DATEDIFF ( CurrentCreatedon, NextTime, SECOND )
    VAR TodayCurrentTime =
        NOW ()
    VAR TimeDiffToNowM =
        DATEDIFF ( CurrentCreatedon, TodayCurrentTime, MINUTE )
    VAR TimeDiffToNowS =
        DATEDIFF ( CurrentCreatedon, TodayCurrentTime, SECOND )
    VAR OutputOthers =
        TRUNC ( TimeDifferenceM / 60, 0 ) & " hrs "
            & MOD ( TimeDifferenceM, 60 ) & " min "
            & MOD ( TimeDifferenceS, 60 ) & " Secs"
    VAR OutputFirst =
        TRUNC ( TimeDiffToNowM / 60, 0 ) & " hrs "
            & MOD ( TimeDiffToNowM, 60 ) & " min "
            & MOD ( TimeDiffToNowS, 60 ) & " Secs"
    RETURN
        IF ( ISBLANK ( TimeDifferenceS ), OutputFirst, OutputOthers )