cancel
Showing results for
Did you mean:
Frequent Visitor

## Help with Time difference between multiple rows

Hi all,

I am looking for some help calculating the time difference between 2 dates for values at different intervals throughout my data. Sample of the data is below.

Example: I need to find the time duration between User ABC = License Count 1 and User ABC = License Count -1, time duration between User XYZ = License Count 1 and User XYZ = License Count -1.

As you can see from the User ADE, there can be some number of rows between a license count = 1 and = -1 for the same user.

Any help gladly appreciated, thank you.

 Timestamp (UTC) File Acquired License Count User 18/12/2017 0:01 Acquired 1 ABC 18/12/2017 0:13 Acquired 1 XYZ 18/12/2017 0:20 Released -1 ABC 18/12/2017 0:31 Acquired 1 ADE 18/12/2017 0:31 Released -1 XYZ 18/12/2017 0:37 Acquired 1 FBA 18/12/2017 0:38 Released -1 FBA 18/12/2017 0:41 Released -1 NRA 18/12/2017 0:41 Acquired 1 NRA 18/12/2017 0:52 Released -1 ADE
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Help with Time difference between multiple rows

Hi @gunner

Here it goes

```Time Difference =
VAR TimeAtAcquisition =
CALCULATE (
FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)], 1 ),
FILTER (
)
)
VAR TimeAtRelease =
CALCULATE (
FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)], 1 ),
FILTER (
)
)
RETURN
DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE )```
13 REPLIES 13
Super User

## Re: Help with Time difference between multiple rows

Hi @gunner

Try this column

```Time Difference =
VAR TimeAtAcquisition =
CALCULATE (
VALUES ( Table1[Timestamp (UTC)] ),
FILTER ( ALLEXCEPT ( Table1, Table1[User] ), Table1[License Count] = 1 )
)
VAR TimeAtRelease =
CALCULATE (
VALUES ( Table1[Timestamp (UTC)] ),
FILTER ( ALLEXCEPT ( Table1, Table1[User] ), Table1[License Count] = -1 )
)
RETURN
DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE )```
Frequent Visitor

## Re: Help with Time difference between multiple rows

Thank you for your reply. I think that will definitely work, except my timestamp column has some duplicate values. Example, 2 User's can acquire a license at the same time. This leads to duplicate entries in the timestamp column and I get 'A table of multiple values was supplied where a single value was expected' when applying your column query.

Is there any way to deal with this?

Thanks again.

Highlighted
Super User

## Re: Help with Time difference between multiple rows

Hi
Please replace VALUES (table1[timestamp]) with firstnonblank (table1[timestamp], 1)
Super User

## Re: Help with Time difference between multiple rows

Hi

I am on mobile so couldnot give you full code. Apologies
Frequent Visitor

## Re: Help with Time difference between multiple rows

Thank you. Are you able to provide the code when you have time? I have changed the values as you suggested, but now I get a syntax error for FILTER being incorrect. I am using:

Time Difference =
VAR TimeAtAcquisition =
CALCULATE (
)
VAR TimeAtRelease =
CALCULATE (
)
RETURN
DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE )

Thank you

Super User

## Re: Help with Time difference between multiple rows

Hi @gunner

Here it goes

```Time Difference =
VAR TimeAtAcquisition =
CALCULATE (
FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)], 1 ),
FILTER (
)
)
VAR TimeAtRelease =
CALCULATE (
FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)], 1 ),
FILTER (
)
)
RETURN
DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE )```
Frequent Visitor

## Re: Help with Time difference between multiple rows

Thank you, I think I am getting somewhere now. I think I am missing just a vital step and I think I'm done. See my results:

The     DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE ) is only returning the difference between the first row of acquired and the first row of released. I put an index field in there just to track the Acquired/Released actions. In each case I get the results of Index 2-1, which is 1 min using the example below.

 EventType DateTime UserID Index Time Difference Acquired 5/12/2017 21:01 abc 1 1 Acquired 6/12/2017 18:52 abc 3 1 Acquired 6/12/2017 21:29 abc 5 1 Acquired 7/12/2017 0:45 abc 7 1 Released 5/12/2017 21:02 abc 2 1 Released 6/12/2017 19:31 abc 4 1 Released 6/12/2017 21:30 abc 6 1 Released 7/12/2017 0:53 abc 8 1

Another example. You can see the timedifference has worked for the the first acquired/released and has returned 3mins, but has carried that value through to every row.

 EventType DateTime UserID Index Time Difference Acquired 11/12/2017 1:03 bm11 1 3 Acquired 11/12/2017 1:06 bm11 3 3 Acquired 11/12/2017 1:31 bm11 5 3 Released 11/12/2017 1:06 bm11 2 3 Released 11/12/2017 1:31 bm11 4 3 Released 11/12/2017 2:31 bm11 6 3

Any ideas appreciated.

Super User

## Re: Help with Time difference between multiple rows

@gunner

Is there an index column in your Original Table with the same pattern you have mentioned above?

Frequent Visitor

## Re: Help with Time difference between multiple rows

Hi. Yes there is an index column. Thanks to your code I managed to get it to work to a usable level. There are some instances where the license acquired and released action can occur for the same user at the exact same time to the second. Presumably the application closes.  In this case the index assigns both values a 1 and tries to subtract required from acquired and then gives a -ve number. Thats fine, as I can filter the report to only show 0 or greater. The time difference calculations work great. Than you for your assistance, I could not have done this without your guidance.

Announcements

#### Community News & Announcements

Get your latest community news and announcements.

#### Power Platform Summit North America

Register by September 5 to save \$200

#### Microsoft Business Applications Virtual Launch Event

Watch the event on demand for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 112 members 1,564 guests
Recent signins: