Average Spacing

[Anonymous]

Spoiler

Spoiler

date/timeHello, 

I am having problems calculating the average spacing between dates 

[v-gizhi-msft]

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]

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 · Accepted solution]

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

[parry2k]

@Anonymous not fully clear what you are trying to do here. Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490