Calculate the time difference in seconds from the current time to the previous time for the same ID.

[Magliari]

How can I create a measure that calculates the difference in seconds from the current Timestamp to the previous Timestamp of the same CallingSsi(ID)?

Exemple:

[quantumudit · Accepted solution]

Hello @Magliari

I hope I've understood your issue correctly. It appears that the first screenshot you provided contains duplicate rows, and since you wish to create a measure to display the time difference.; assuming your table has only two columns, I have devised this solution for you.

I appreciate your attempt to clarify the scenario with a smaller example in the second screenshot. However, the time difference you've calculated there is incorrect because you only considered the time, not the date.

I would really appreciate it if you could take some time to carefully formulate your questions and provide appropriate, anonymized sample data and the expected results you need. Otherwise, we may have to make assumptions that might not be correct. Please adhere to the instructions provided by @lbendlin 

I have created a DAX measure to calculate the time difference in seconds for "CallingSsi" from its last available point.

Timestamp Difference (in Seconds) = 
VAR _filteredTbl =
    GROUPBY (
        FILTER (
            ALL ( TimestampTbl ),
            TimestampTbl[Timestamp] <= MIN ( TimestampTbl[Timestamp] )
                && TimestampTbl[CallingSsi] = MIN ( TimestampTbl[CallingSsi] )
        ),
        TimestampTbl[Timestamp],
        TimestampTbl[CallingSsi]
    )
VAR _top2rowsTbl =
    SELECTCOLUMNS (
        TOPN ( 2, _filteredTbl, TimestampTbl[Timestamp], DESC ),
        "timestamp", TimestampTbl[Timestamp]
    )
VAR _currentTimestamp =
    TOPN ( 1, _top2rowsTbl, [timestamp], DESC )
VAR _previousTimestamp =
    TOPN ( 1, _top2rowsTbl, [timestamp], ASC )
RETURN
    DATEDIFF ( _previousTimestamp, _currentTimestamp, SECOND )

Here is the screenshot of the solution:

I hope this will offer the solution you require. If you continue to encounter issues, please inform us and supply a relevant sample dataset along with the expected outcomes for us to fully comprehend the problem.

Best Regards,
Udit

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudo 👍

🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
✨ Visit My Linktree: LinkTree

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...