cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sparkbymahi Occasional Visitor
Occasional Visitor

Re: Help with Time difference between multiple rows

Hi 

 

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

gunner Frequent Visitor
Frequent Visitor

Re: Help with Time difference between multiple rows

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

Highlighted
gunner Frequent Visitor
Frequent Visitor

Re: Help with Time difference between multiple rows

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

quyennguyen New Member
New Member

Re: Help with Time difference between multiple rows

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