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.
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.