Forum Discussion
Challenge time difference calculation in Power BI
OK, for this you are going to have to make sure that in M (Power Query) that your Time column is set to Duration. Then in the model you need to make sure that these are tagged as decimal numbers. Then, you are going to need to use EARLIER to get the previous row. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
Once you can return the previous time, you can then use the formula:
([Time] - var_PreviousTime) * 60 * 24 * 60
Note that when you have a Date/Time kind of thing, the Date portion of this is the number of days since 12/30/1899. The time portion is the decimal portion. You can convert this to hours by 60*24. Then, to convert to minutes you multiply again by 60.
In the example above, var_PreviousTime is your previous time value that you find using EARLIER.
OK, for this you are going to have to make sure that in M (Power Query) that your Time column is set to Duration. Then in the model you need to make sure that these are tagged as decimal numbers. Then, you are going to need to use EARLIER to get the previous row. See my article on Mean Time Before Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
Once you can return the previous time, you can then use the formula:
([Time] - var_PreviousTime) * 60 * 24 * 60
Note that when you have a Date/Time kind of thing, the Date portion of this is the number of days since 12/30/1899. The time portion is the decimal portion. You can convert this to hours by 60*24. Then, to convert to minutes you multiply again by 60.
In the example above, var_PreviousTime is your previous time value that you find using EARLIER.
Massive thanks Greg_Deckler, it's my great pleasure to receive your help.
I am starting to learn your MTBF method, it looks like very useful.
One more concern, based one the duration results, how can I achieve the Calculation column in Power BI?
Many thanks again.
