cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
gunner Frequent Visitor
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 AcquiredLicense CountUser
18/12/2017 0:01Acquired1ABC
18/12/2017 0:13Acquired1XYZ
18/12/2017 0:20Released-1ABC
18/12/2017 0:31Acquired1ADE
18/12/2017 0:31Released-1XYZ
18/12/2017 0:37Acquired1FBA
18/12/2017 0:38Released-1FBA
18/12/2017 0:41Released-1NRA
18/12/2017 0:41Acquired1NRA
18/12/2017 0:52Released-1ADE
1 ACCEPTED SOLUTION

Accepted Solutions
Super User
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 (
            ALLEXCEPT ( 'LicenseUsage', 'LicenseUsage'[User] ),
            'LicenseUsage'[Action] = 1
        )
    )
VAR TimeAtRelease =
    CALCULATE (
        FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)], 1 ),
        FILTER (
            ALLEXCEPT ( 'LicenseUsage', 'LicenseUsage'[User] ),
            'LicenseUsage'[Action] = -1
        )
    )
RETURN
    DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE )
13 REPLIES 13
Super User
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 )
gunner Frequent Visitor
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
Super User

Re: Help with Time difference between multiple rows

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

Re: Help with Time difference between multiple rows

Hi
Please Replace in both variables

I am on mobile so couldnot give you full code. Apologies
gunner Frequent Visitor
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 (
FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)],1)
FILTER ( ALLEXCEPT ('LicenseUsage', 'LicenseUsage'[User] ), 'LicenseUsage'[Action] = 1 )
)
VAR TimeAtRelease =
CALCULATE (
FIRSTNONBLANK ('LicenseUsage'[Timestamp (UTC)],1 )
FILTER ( ALLEXCEPT ( 'LicenseUsage', 'LicenseUsage'[User]), 'LicenseUsage'[Action] = -1 )
)
RETURN
DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE )

 

Thank you

Super User
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 (
            ALLEXCEPT ( 'LicenseUsage', 'LicenseUsage'[User] ),
            'LicenseUsage'[Action] = 1
        )
    )
VAR TimeAtRelease =
    CALCULATE (
        FIRSTNONBLANK ( 'LicenseUsage'[Timestamp (UTC)], 1 ),
        FILTER (
            ALLEXCEPT ( 'LicenseUsage', 'LicenseUsage'[User] ),
            'LicenseUsage'[Action] = -1
        )
    )
RETURN
    DATEDIFF ( TimeAtAcquisition, TimeAtRelease, MINUTE )
gunner Frequent Visitor
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. 

 

EventTypeDateTimeUserIDIndexTime Difference
Acquired5/12/2017 21:01abc11
Acquired6/12/2017 18:52abc31
Acquired6/12/2017 21:29abc51
Acquired7/12/2017 0:45abc71
Released5/12/2017 21:02abc21
Released6/12/2017 19:31abc41
Released6/12/2017 21:30abc61
Released7/12/2017 0:53abc81

 

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.

 

EventTypeDateTimeUserIDIndexTime Difference
Acquired11/12/2017 1:03bm1113
Acquired11/12/2017 1:06bm1133
Acquired11/12/2017 1:31bm1153
Released11/12/2017 1:06bm1123
Released11/12/2017 1:31bm1143
Released11/12/2017 2:31bm1163

 

Any ideas appreciated.

Super User
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?

gunner Frequent Visitor
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.

Helpful resources

Announcements
Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

Virtual Launch Event

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.

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 112 members 1,564 guests
Please welcome our newest community members: