Forum Discussion

RichardT_78's avatar
RichardT_78
Helper I
5 years ago
Solved

Time

Hi 

I have used this code to determine the time difference between rows as a messure. 

 

This looks at each row of data. 

 

Time Difference Measure13 =
VAR NextRow =
CALCULATE (
MIN ( FAC[Start Date/Time] ),
FILTER (
ALL ( FAC ),
FAC[Start Date/Time] > SELECTEDVALUE ( FAC[Start Date/Time] )
)
)
RETURN
IF (
SELECTEDVALUE ( FAC[Consist] ) = 0,
DATEDIFF ( SELECTEDVALUE ( FAC[Start Date/Time] ), NextRow, SECOND )
)
 
 

How would i determine the difference when using a slicer? The index is showing what lines the data is on just for me to understand what the messure was doing.

 

If i select consist using a slicer i would like the messure to determin the time difference.

 

 

Any help would be appricated - i have spent hours trying to find a solution.

General Comment
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hello @RichardT_78 ,

    You can update your measurement as below screenshot shown, please find more details in the attachment:

    Time Difference = 
VAR _nexindex =
    CALCULATE (
        MIN ( 'FAC'[Index] ),
        FILTER (
            ALLEXCEPT ( 'FAC', 'FAC'[Consist] ),
            'FAC'[Index] > MAX ( 'FAC'[Index] )
        )
    )
VAR _nexttime =
    CALCULATE (
        MAX ( 'FAC'[Start Date/Time] ),
        FILTER ( ALLEXCEPT ( 'FAC', 'FAC'[Consist] ), 'FAC'[Index] = _nexindex )
    )
RETURN
    DATEDIFF ( SELECTEDVALUE ( 'FAC'[Start Date/Time] ), _nexttime, SECOND ) + 0

    Time.JPG

    Best regards

5 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    RichardT_78 ,The information you have provided is not making the problem clear to me. Can you please explain with an example.

    Appreciate your Kudos.

    Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • RichardT_78's avatar
    RichardT_78
    Helper I
    5 years ago

    ConsistStart Date/Time
    37743601/12/2020 13:08:51
    37743601/12/2020 13:08:50
    37715301/12/2020 13:06:32
    37715301/12/2020 13:06:30
    37740801/12/2020 13:04:51
    37743601/12/2020 13:04:40
    37743601/12/2020 13:04:39
    37714001/12/2020 13:02:14
    37714001/12/2020 13:02:12
    37743601/12/2020 13:01:00
    37743601/12/2020 13:00:59
    37742401/12/2020 12:59:54
    37742401/12/2020 12:59:51
    37744701/12/2020 12:59:40
    37710401/12/2020 12:59:36

    377436 would be a good test as its spread over the table  

     

    So are for example 100 consist codes, which all have a time stamp for each particular event.

    When I use a slicer and select 377104 for example, it displays all events chronologically, but the messure fails. 

    As you can see the messure works fine when you dont focus on a consist, but when i select a consist its rows are not consecutive.

     

    Hope this helps , much appricated 

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello @RichardT_78 ,

      You can update your measurement as below screenshot shown, please find more details in the attachment:

      Time Difference = 
VAR _nexindex =
    CALCULATE (
        MIN ( 'FAC'[Index] ),
        FILTER (
            ALLEXCEPT ( 'FAC', 'FAC'[Consist] ),
            'FAC'[Index] > MAX ( 'FAC'[Index] )
        )
    )
VAR _nexttime =
    CALCULATE (
        MAX ( 'FAC'[Start Date/Time] ),
        FILTER ( ALLEXCEPT ( 'FAC', 'FAC'[Consist] ), 'FAC'[Index] = _nexindex )
    )
RETURN
    DATEDIFF ( SELECTEDVALUE ( 'FAC'[Start Date/Time] ), _nexttime, SECOND ) + 0

      Time.JPG

      Best regards

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hello,

     

    I am using a crime dataset and trying to put it in a star schema. However, when I merge the dimension table with a fact table it causes the fact table to add additional rows. This dataset has three different date columns and three different time columns. I have tried so many different ways and got the same results. Here is a screenshot of the data model. And the file to the original dataset.

     

    https://data.brla.gov/Public-Safety/Baton-Rouge-Crime-Incidents/pbin-pcm7/data