Working with timestamp data - looking at average duration. Please help!

Hi  clewis ,

Test below :

Create the data column:

DATE1 = FORMAT('Table'[Log Timestamp],"yyyymmdd")

Then create the below measure:

test = var datediff1=DATEDIFF(
CALCULATE (
        MIN ( 'Table'[Visit Start Timestamp] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[IP ADDRESS] = MAX ( 'Table'[IP ADDRESS] )
                && FORMAT ( 'Table'[Log Timestamp], "yyyymmdd" )
                    = FORMAT ( MAX ( 'Table'[Log Timestamp] ), "yyyymmdd" )
        )),CALCULATE (
    MAX ( 'Table'[Visit End Timestamp] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[IP ADDRESS] = MAX ( 'Table'[IP ADDRESS] )
            && FORMAT ( 'Table'[Log Timestamp], "yyyymmdd" )
                = FORMAT ( MAX ( 'Table'[Log Timestamp] ), "yyyymmdd" )
    )),
MINUTE)/60     return datediff1

Measure = AVERAGEX(FILTER(ALL('Table'),'Table'[DATE1]=MAX('Table'[DATE1])),[test])

Final output:

You could download my pbix file if you need.

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

Hello there clewis ! I hope I understood your question correctly. Try this:

Average =
var _min = MIN( Table[Start Timestamp] )
var _max = MAX( Table[End Timestamp] )
var _log = SELECTEDVALUE( Table[Log Timestamp] )

return
CALCULATE(
AVERAGEX( Table, _min + _max ),
Table[Log Timestamp] = _log
)

  If the CALCULATE() does not work try with an IF()!

Hope this answer solves your problem! If you need any additional help please tag me in your reply.
If my reply provided you with a solution, pleased mark it as a solution ✔️ or give it a kudoe 👍
Thanks!

Best regards,
Gonçalo Geraldes

Hi clewis ，

In my opinion, you can count the number of unduplicated IPs in the average duration.

In the sample ,the same measure（May have to be rounded up or down, depending on whether you need） with distincount Ip.

Best Regards

Lucien

Hi clewis ,

Could you pls share your pbix file ,and remove confidential data.

Best Regards

Lucien