Forum Discussion
Challenge time difference calculation in Power BI
Hi SmartBrain,
I am not sure how to calculate the time difference and apply certain calculation based on results in Power BI.
Data sample is shown below, column Label and Time are already in query table. I want to calculate the adjacent row time difference and store in Difference column with seconds results, and calculate these result by (x + y)/(x*y), then store the results in Calculation column. Desirable results are shown in Difference and Calculation columns, I don't know how to implement them in Power BI.
Can you help me to solve it please? Your generous help will be extremely appreciated. Many thanks in advance.
Regards
Newbee
Label Time Difference Calculation
A1 00:03:00 0 0
A2 00:05:00 120 (0 + 120) / (0 * 120)
B1 00:08:00 180 0
B2 00:09:30 90 (180 + 90) / (180 * 90)
C1 00:10:00 30 0
C2 00:15:00 300 (30 + 300) / (30 * 300)
B1 00:17:00 120 0
B2 00:21:00 240 (120 + 240) / (120 * 240)
...... ...... ...... .......
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.
4 Replies
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.
The first Challange (Time duration) is finished, many thanks.
How to solve the second one? calculate the (x + y) / (x * y) though the entire column in POWER BI?
Much appreciate for your efforts.
Regards
- Ah, I missed that, I thought that was just there in terms of explanantion. I don't see any way to do that other than a bunch of nested CONCATENATE statements. I would create all of the necessary elements as VAR's and then just CONCATENATE them together or use the & operator.
