Thanks for the idea.Īlso, please see this article for an easy way to calculate your total duration from a table like this (without adding a duration column subtracting the two time columns). This is a fairly common use case, and a good topic for a video. A model like this should simplify other calculations too (e.g., days in operation). You can then relate the one date column to your Date table. It works even if the operation spans 3+ days. Where there is a row for every date a line is in operation. It shows how to convert a table like this #"Split Column by Delimiter" = Table.SplitColumn(Source, "StartTime", Splitter.SplitTextByEachDelimiter() Week Number Calculator Find the week number for any date. Weekday Calculator What day is this date Birthday Calculator Find when you are 1 billion seconds old. Date Calculator Add or subtract days, months, years. Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI00Dc01TcyMDJUsLAyNFBw9EUVNDS0MgKLxuogNJghaQiAaTCHCJpYGcPVO6GZZYCiAWqKEdRaZA1QKXOo3RBBC4igMdSQ2FgA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta ) in type table ), Time and Date Duration Calculate duration, with both date and time included. For an example, create a blank query, open the advanced editor and replace the M code there with this. If you can transform your data further, you can simplify things. If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. + CALCULATE ( SUM ( ), ALLEXCEPT ( 'Table', 'Table' ) ) MAX ( ) = BLANK (), BLANK (),ĭATEDIFF ( MAX ( ), _end, DAY ) >= 1,ĭATEDIFF ( DATE ( YEAR ( _end ), MONTH ( _end ), DAY ( _end ) ), _end, MINUTE ) / 60,0)))ģ.create a table. Processing Time (start) = var _maxstart=MAX()ĭATEDIFF ( _maxstart, MAX ( ), MINUTE ) / 60,ĭATEDIFF(_maxstart,MAX(),DAY)>=1,DATEDIFF(_maxstart,DATE(YEAR(_maxstart),MONTH(_maxstart),DAY(_maxstart)+1),MINUTE)/60,ĭATEDIFF (_maxstart, MAX ( ), MINUTE ) / 60Ģ.create a datediff base on endtime. The interval involves two days, you can use if(), and then use the next day (0:00:00) as the end time, and the next day (0:00:00) as the end time as the starting time, so you can create two measures, one to calculate the daily interval of startTime and one to calculate the daily interval of EndTime, and then summarize them as follows:ġ.create a datediff base on starttime.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |