Dwell time analysis

[DrTammy]

I am new to Power Bi and I really appreciatte if you could help me with my problem. I need to analyse the dwell time for a number of bikes in shared bike program. 

[v-juanli-msft · Accepted solution]

Hi @DrTammy 

If you'd like result below:

average hours spent in cities before the selected date period for every bike id.

Measure below:

average time =
CALCULATE (
    SUM ( Sheet4[hours] ),
    FILTER (
        ALLEXCEPT ( Sheet4, Sheet4[bike id] ),
        Sheet4[end datetime].[Date] <= MIN ( 'date'[Date] )
    )
)
    / CALCULATE (
        COUNT ( Sheet4[bike id] ),
        FILTER (
            ALLEXCEPT ( Sheet4, Sheet4[bike id] ),
            Sheet4[end datetime].[Date] <= MIN ( 'date'[Date] )
        )
    )

"Date" is a table without relationship between "date" and "Sheet3",

"hours" is a column created in transform data

Duration.TotalHours([end datetime]-[start datetime])

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@DrTammy , refer if these can help

https://community.powerbi.com/t5/Desktop/Dwell-time-analysis-of-network/td-p/1126540

https://community.powerbi.com/t5/Desktop/Finding-dwell-time/td-p/907482

[Fowmy]

@DrTammy 

Please check this post, it solves somewhat a similar problem:

https://community.powerbi.com/t5/Desktop/Finding-dwell-time/td-p/907482

Meantime, Can you share some sample data and the expected result as data to have a clear understanding of your question?

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn

[DrTammy]

Thanks for your reply but I have aleady checked that post but it did not help me! 

[Fowmy]

@DrTammy 

Can you explain how the expected result should be calculated 

You have got start Date/Time and End Date/Time and Duration.

[DrTammy]

@Fowmy I have hundreds of bikes with distinct bike numbers and each bike has made several trips through the city. I have the trips details  including the date and time of start and end points. Probably I need to calculate the dwell time for the bike before it goes on the next trip then take the average of dwell times.

@amitchandakI have already checked that as well but It did not work for my case, it gave me negative hours!