Help with Time difference between multiple rows

[gunner]

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

[Zubair_Muhammad]

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]

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.

[Zubair_Muhammad]

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

[Zubair_Muhammad]

Hi
Please Replace in both variables

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

[gunner]

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

[Zubair_Muhammad · Accepted solution]

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]

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.

[Zubair_Muhammad]

@gunner

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

[gunner]

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.

[sparkbymahi]

Hi 

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

[gunner]

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 (
    OR ( 'OpenLicenseUsage-9601427'[Action] = "Released", 'OpenLicenseUsage-9601427'[Action] = "Acquired" ),
    RANKX (
        FILTER (
            ALL ( 'OpenLicenseUsage-9601427'),
            OR ( 'OpenLicenseUsage-9601427'[Action] = "Released", 'OpenLicenseUsage-9601427'[Action] = "Acquired" )
                && 'OpenLicenseUsage-9601427'[User] = EARLIER ( 'OpenLicenseUsage-9601427'[User] )
        ),
        'OpenLicenseUsage-9601427'[Timestamp (UTC)],
        ,
        asc
    )
)

[quyennguyen]

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

[gunner]

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 (
    OR ( 'OpenLicenseUsage-9601427'[Action] = "Released", 'OpenLicenseUsage-9601427'[Action] = "Acquired" ),
    RANKX (
        FILTER (
            ALL ( 'OpenLicenseUsage-9601427'),
            OR ( 'OpenLicenseUsage-9601427'[Action] = "Released", 'OpenLicenseUsage-9601427'[Action] = "Acquired" )
                && 'OpenLicenseUsage-9601427'[User] = EARLIER ( 'OpenLicenseUsage-9601427'[User] )
        ),
        'OpenLicenseUsage-9601427'[Timestamp (UTC)],
        ,
        asc
    )
)