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 )
ahmedoye's avatar
ahmedoye
Responsive Resident
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 )
RhodesBrown's avatar
RhodesBrown
Frequent Visitor
3 years ago

Thank so much, that was perfect from a text point of view. I then just added a further column to convert into decimals to create analysis on the back of this

  • Iamadityarajput's avatar
    Iamadityarajput
    Frequent Visitor
    2 years ago

    Hi RhodesBrown 

     

    I'm getting a message "not enough to perform this operation" my VM configuration is 16 GB, is it sufficient?