Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Average Spacing

[ Spoiler ] Hello,    I am having problems calculating the average spacing between dates 
Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    Hi,

     

    Please take following steps:

    1)Add an index column to the original table in Query Editor.

    2)Try this column to combine date and time columns:

    Date-Time = 'Table'[Date]+'Table'[Time]

    3)Try this calcluated column:

    Column =
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[Date-Time] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    ),
    'Table'[Date-Time],
    SECOND
)

    4)The result shows:

    See my attached pbix file.

     

    Best Regards,

    Giotto

     

v-gizhi-msft's avatar
v-gizhi-msft
Community Support
6 years ago

Hi,

 

Could you please explain more about your requirement?

You can share your expected result as a screenshot.

Expect your reply!

 

Best Regards,

Giotto

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hello, parry2k , v-gizhi-msft  

     

     I want to calculate the date difference (in minutes) between each date in the column shown.

     

    For example:  

    Date               Time           Results in seconds  

    08/04/2020  15:52:20       

    08/04/2020  15:52:20        0

    08/04/2020  15:52:22        2

    08/04/2020  15:22:24        2  

    08/04/2020  15:24:00       96

    09/04/2020  15:24:00        86 400      (24 hours in seconds)

    09/04/2020  15:24:25        25

    09/04/2020  15:25:25       60

    09/04/2020  15:29:25       240

    09/04/2020  15:29:25       0

     

    Thank you in advance

    Best, 

    Mark

     

     

     

    • v-gizhi-msft's avatar
      v-gizhi-msft
      Community Support
      6 years ago

      Hi,

       

      Please take following steps:

      1)Add an index column to the original table in Query Editor.

      2)Try this column to combine date and time columns:

      Date-Time = 'Table'[Date]+'Table'[Time]

      3)Try this calcluated column:

      Column =
DATEDIFF (
    CALCULATE (
        MAX ( 'Table'[Date-Time] ),
        FILTER ( 'Table', 'Table'[Index] = EARLIER ( 'Table'[Index] ) - 1 )
    ),
    'Table'[Date-Time],
    SECOND
)

      4)The result shows:

      See my attached pbix file.

       

      Best Regards,

      Giotto