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 (Relationship Consultant Engage + Input) being 22/03/2023 16:39:30 minus 22/03/2023 08:35:19. And then if the new status = the current status e.g row 1, how long has it been in that status. This obviously needs to be specific to the Name column. Any help would be appreciated

 

 

 

NameNew StatusOld StatuscreatedonCurrent Status
Gastropay LimitedFull Application CreatedRelationship Consultant Engage + Input22/03/2023 16:39:30Full Application Created
Gastropay LimitedRelationship Consultant Engage + InputFinal Risk Assessment + Sign Off22/03/2023 08:35:19Full Application Created
Gastropay LimitedFinal Risk Assessment + Sign OffRelationship Consultant Engage + Input22/03/2023 08:35:14Full Application Created
Gastropay LimitedRelationship Consultant Engage + InputAwaiting Client Sign Off22/03/2023 08:35:12Full Application Created
Paynes Trains LimitedClient Account CreationAwaiting Client Sign Off15/03/2023 14:49:27Client Account Creation
Paynes Trains LimitedAwaiting Client Sign OffFinal Risk Assessment + Sign Off15/03/2023 14:49:16Client Account Creation
Paynes Trains LimitedFinal Risk Assessment + Sign OffRelationship Consultant Engage + Input15/03/2023 14:29:55Client Account Creation
Paynes Trains LimitedRelationship Consultant Engage + InputFull Application Created15/03/2023 09:55:10Client Account Creation
Paynes Trains LimitedFull Application CreatedInitial Application15/03/2023 09:55:04Client Account Creation
Firestone Property LimitedFinal Risk Assessment + Sign OffRelationship Consultant Engage + Input14/03/2023 13:05:04Final Risk Assessment + Sign Off
Firestone Property LimitedRelationship Consultant Engage + InputFinal Risk Assessment + Sign Off13/03/2023 15:55:44Final Risk Assessment + Sign Off
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 )

4 Replies

  • 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?