Time in Status

[RhodesBrown]

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

Name	New Status	Old Status	createdon	Current Status
Gastropay Limited	Full Application Created	Relationship Consultant Engage + Input	22/03/2023 16:39:30	Full Application Created
Gastropay Limited	Relationship Consultant Engage + Input	Final Risk Assessment + Sign Off	22/03/2023 08:35:19	Full Application Created
Gastropay Limited	Final Risk Assessment + Sign Off	Relationship Consultant Engage + Input	22/03/2023 08:35:14	Full Application Created
Gastropay Limited	Relationship Consultant Engage + Input	Awaiting Client Sign Off	22/03/2023 08:35:12	Full Application Created
Paynes Trains Limited	Client Account Creation	Awaiting Client Sign Off	15/03/2023 14:49:27	Client Account Creation
Paynes Trains Limited	Awaiting Client Sign Off	Final Risk Assessment + Sign Off	15/03/2023 14:49:16	Client Account Creation
Paynes Trains Limited	Final Risk Assessment + Sign Off	Relationship Consultant Engage + Input	15/03/2023 14:29:55	Client Account Creation
Paynes Trains Limited	Relationship Consultant Engage + Input	Full Application Created	15/03/2023 09:55:10	Client Account Creation
Paynes Trains Limited	Full Application Created	Initial Application	15/03/2023 09:55:04	Client Account Creation
Firestone Property Limited	Final Risk Assessment + Sign Off	Relationship Consultant Engage + Input	14/03/2023 13:05:04	Final Risk Assessment + Sign Off
Firestone Property Limited	Relationship Consultant Engage + Input	Final Risk Assessment + Sign Off	13/03/2023 15:55:44	Final Risk Assessment + Sign Off

[ahmedoye · Accepted solution]

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]

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]

Hi @RhodesBrown 

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

[RhodesBrown]

@amitchandak anything you can help with?