Time in between Formula

[nataliesmiy1357]

Good afternoon!

I am looking to create a formula/measure to capture how much time is between each row/entry.

I have the AI_Start_Time and then AI_Norm_Time.

I need to make a formula that shows the difference in time between entry 1's AI_Norm_Time, and entry 2's AI_Start_Time

(ex: Time between 10/14/2023 6:35:54AM and 10/14/2023 6:46:36AM = 10 minutes and 42 seconds.)

Entry Number	AI_Start_Time	AI_Norm_Time
1	10/14/2023 6:35:52AM	10/14/2023 6:35:54AM
2	10/14/2023 6:46:36AM	10/14/2023 6:46:38AM

[mark_endicott · Accepted solution]

@nataliesmiy1357 - it's not clear how you want to present this, but I've attached a PBIX file with a solution. 

It shows the time in DD:HH:MM:SS format

If this works for you please accept as the solution. It helps others with the same challenge. 

[mark_endicott]

@nataliesmiy1357 - Did either me or @Jihwan_Kim  solve this for you?

Accepting the solution helps other people find the answers on this forum, so please do this or let us know what else you need.

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

It is for creating a new column.

OFFSET function (DAX) - DAX | Microsoft Learn

Difference in seconds CC =
VAR _normtime = Data[AI_Norm_Time]
VAR _nextrowstarttime =
    MAXX (
        OFFSET (
            1,
            Data,
            ORDERBY ( Data[Entry Number], ASC ),
            ,
            ,
            MATCHBY ( Data[Entry Number] )
        ),
        Data[AI_Start_Time]
    )
RETURN
    DATEDIFF ( _normtime, _nextrowstarttime, SECOND )

[mark_endicott · Accepted solution]

@nataliesmiy1357 - it's not clear how you want to present this, but I've attached a PBIX file with a solution. 

It shows the time in DD:HH:MM:SS format

If this works for you please accept as the solution. It helps others with the same challenge.