cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Community Champion

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 )```

Regards
Zubair

13 REPLIES 13
Community Champion

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 )```

Regards
Zubair

Frequent Visitor

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.

Community Champion
Hi
Please replace VALUES (table1[timestamp]) with firstnonblank (table1[timestamp], 1)

Regards
Zubair

Community Champion
Hi

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

Regards
Zubair

Frequent Visitor

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

Community Champion

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 )```

Regards
Zubair

Frequent Visitor

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.

Community Champion

@gunner

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

Regards
Zubair

Frequent Visitor

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.

New Member

Hi

Can you share the code that you used to calculate difference with the index as I have same requriement

Frequent Visitor

Hi guys, I had previously marked this as solved but I have noticed something which is throwing off my calculations. I am using an index field to calculate the difference between the times of an item being acquired and it being released. The query language for the index is from earlier in this thread and it works fine until an item is acquired and released at the exact same time for a user. The Index assigns both actions the same value, which throws out the calculations quite substantially.

Example in the image below, where the last 2 lines an item is acquired and released at same time and both are assigned the same index value (17). Ideally i want the index to assign the value of 17&18, or even just disregard this altogether as the corresponding time value would be zero. I have tested it by manually deleting those last 2 entries from the datasource and the calculations are perfect. I need same values to be removed, or assigned the correct index value. Thank you if you can help.

Index Query:

```Index =
IF (
RANKX (
FILTER (
),
,
asc
)
)```

New Member

Hi,

First of all thanks for this information.

Could you please share the formula where you included the INDEX column as well..?

And for your Index problem, if your timefield has miliseconds, maybe thats a way to make it unique.

Kind regards,

Quyen

Frequent Visitor

Hi guys, I had previously marked this as solved but I have noticed something which is throwing off my calculations. I am using an index field to calculate the difference between the times of an item being acquired and it being released.

The query language for the index is from earlier in this thread and it wokrs fine until an item is acquired and released at the exact same time for a user. The Index assigns both actions the same value, which throws out the calculations quite substantially. Example in the image below, where the last 2 lines an item is acquired and released at same time and both are assigned the same index value (17). Ideally i want the index to assign the value of 17&18, or even just disregard this altogether as the corresponding time value would be zero. I have tested it by manually deleting those last 2 entries from the datasource and the calculations are perfect. I need same values to be removed, or assigned the correct index value. Thank you if you can help.

Index Query:

```Index =
IF (
RANKX (
FILTER (
),
,
asc
)
)```

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.