Forum Discussion

bergen288's avatar
bergen288
Icon for Helper IV rankHelper IV
3 years ago

Use DATEDIFF function to calculate session gap between previous end time and current start time

I have a table visual as shown below where Created_TS is the session start time and Created_TS_End is session end time.  I would like to use DATEDIFF function to calculate session gap between previous end time and current start time for each row as shown in 2 red box below after the data is sorted by Created_TS.  Is there an easy way to do it?

 

 

Need Help

2 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

    Please chech the below picture and the attached pbix file.

    It is for creating a calculated column.

     

     

    Expected result CC =
VAR _prevstart =
    MAXX (
        FILTER ( Data, Data[Created_TS] < EARLIER ( Data[Created_TS] ) ),
        Data[Created_TS]
    )
VAR _prevend =
    MAXX ( FILTER ( Data, Data[Created_TS] = _prevstart ), Data[Created_TS_End] )
VAR _start = Data[Created_TS]
VAR _diffinminutes =
    DATEDIFF ( _prevend, _start, MINUTE )
RETURN
    _diffinminutes

     

     

     

  • bergen288's avatar
    bergen288
    Icon for Helper IV rankHelper IV
    3 years ago

    My data is pretty large one over 710,000 rows.  It takes very long time to create the new column (still waiting for it).  I believe a measure should have a much better performance as it will be used with a selected case via drill-through.  So I tried to define the following measure, but it gives me an error.  The root cause should be the 1st row which won't have previous (earlier) value.  How to address it?

     

    Thanks.