Date difference (minutes) between date stamps in different rows

[Markus44]

Hello Community,

I have a case to which I can find plenty of information in this community, however, I am still not able to solve it. I am quite new to Power BI DAX formulas so sorry for that possibly stupid question.

My case:

I have many rows with individual date time stamps.

Each row also has a column namend "currentCount" as well as a column named "prevCount".

So it kinda looks like this:

ID	currentCount	prevCount	datetimestamp	Difference
1	5	4	dd/mm/yyyy hh:mm:ss
2	3	2	dd/mm/yyyy hh:mm:ss
3	6	5	dd/mm/yyyy hh:mm:ss
4	2	1	dd/mm/yyyy hh:mm:ss
5	4	3	dd/mm/yyyy hh:mm:ss

What I am looking for is the time difference in minutes between the currentCount and the prevCount.

From my research I assume it will somehow work using EARLIER, but as this is my first time using it I am having a hard time.

Hope my direction is correct and someone can help me out.

Thanks in advance and regards

Markus

[Kedar_Pande]

Make sure your table is sorted by the datetimestamp column in ascending order so that each row correctly follows the previous one.

Add a Calculated Column:

Time Difference (Minutes) = 
VAR CurrentRowDate = 'YourTable'[datetimestamp]
VAR PreviousRowDate = 
CALCULATE(
MAX('YourTable'[datetimestamp]),
FILTER(
'YourTable',
'YourTable'[ID] < EARLIER('YourTable'[ID])
)
)
RETURN
IF (
ISBLANK(PreviousRowDate), 
BLANK(), 
DATEDIFF(PreviousRowDate, CurrentRowDate, MINUTE)
)

If this helped, a Kudos 👍or a Solution mark would be awesome! 🎉
Cheers,
Kedar Pande
Connect on LinkedIn

[rajendraongole1 · Accepted solution]

Hi @Markus44 -No worries, you're on the right track, but using EARLIER might not be necessary for this case. Instead, you can compute the time difference between each row's datetimestamp in minutes directly using DAX without EARLIER

create a calculated column for the time difference in minutes

TimeDifferenceInMinutes =

VAR CurrentRowTimestamp = 'timedin'[datetimestamp]

VAR PreviousRowTimestamp =

    CALCULATE(

        MAX('timedin'[datetimestamp]),

        FILTER(

            'timedin',

            'timedin'[currentCount] = EARLIER('timedin'[prevCount])

        )

    )

RETURN

IF(

    NOT ISBLANK(PreviousRowTimestamp),

    DATEDIFF(PreviousRowTimestamp, CurrentRowTimestamp, MINUTE),

    BLANK()

)

Hope this helps