Implementation of a binary filter (0/1) with a condition over a variable sliding period

[Gaïd]

Hello everyone!

I'm currently working on a dataset of over 11M rows. 1 row represents what we call a log (the consumption and the GPS location for a car). A car sends us a log every 5min but some of the logs are missing.

The columns of the dataset are the following:

• Numero_car: number assigned to a car
• Date_echantillonnage: date and time of creation of a log
• Latitude: latitude at the time of creation of the log
• Longitude: longitude at the time of creation of the log
• Consumption: fuel consumed by the car during 5 min (between 2 logs).

For a car, when all the logs are null for 24 hours, I’d like to remove those logs of my calculation by implementing a binary filter (0/1). 

Does anyone have an idea on how to proceed to implement such a thing in Power BI Desktop?

Thank you for your help!

[Greg_Deckler · Accepted solution]

Tough to say exactly without sample/example data but check out the technique here:

http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395...

Basically, you should be able to use that technique to calculate how long between logs essentially.