Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gunner
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

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 )

Regards
Zubair

Please try my custom visuals

View solution in original post

13 REPLIES 13
Zubair_Muhammad
Community Champion
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

Please try my custom visuals

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.

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

Regards
Zubair

Please try my custom visuals

Hi
Please Replace in both variables

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

Regards
Zubair

Please try my custom visuals

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

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 )

Regards
Zubair

Please try my custom visuals

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.

@gunner

 

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


Regards
Zubair

Please try my custom visuals

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.

Hi 

 

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

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

Capture.JPG

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

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

 

Capture.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.