Forum Discussion
Help with Time difference between multiple row per day and month
I'm not sure what other analyses you'll need to do with your data, but for the one you asked about, I would pivot your data so you get Enter and Exit on the same row, so you can use Duration.TotalHours() to calculate the duration. Please put this M code into a blank query to see how to do it with your data. Note that I did OCR to extract your data from the pic but it didn't split all the columns so I had to do extra steps in the beginning to address that (that you won't need to do with your data).
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("ddK7bgMhEAXQX7G2tuR5MI+lWyku3KRI0ln+/98IYMW7DjMV6CIdXQbu94WAAFCLFnFczgsjqHrboF4AL+PYavEKcNqjdt7Dtlw/f762j215nA+WElJsIVUokzXStn5vt/+UoSXUCTm0+GmFxdxVk2JaZb7kSMNiBlbkjbJXMWgQD8sOVg+TXkYOmGBtNoUmbKRxMVaHzOK/l7T3iaVPaSLCmeaVy6z1NG5mWtajRXD4YoKd2qPxxQSzXqupxVYbDa2TNdKwloOhJxSHFD+pqJcLcHLHPi+iWevpXuzxCw==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [CHAVE = _t, MAT = _t, HORA = _t, #"4 KORA" = _t, #"4 FL_CTR_ENTRADA_SAIDA" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"CHAVE", Int64.Type}, {"MAT", Int64.Type}, {"HORA", type text}, {"4 KORA", type time}, {"4 FL_CTR_ENTRADA_SAIDA", type text}}),
#"Split Column by Delimiter" = Table.SplitColumn(#"Changed Type", "HORA", Splitter.SplitTextByEachDelimiter({" "}, QuoteStyle.Csv, true), {"HORA.1", "HORA.2"}),
#"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"HORA.1", type text}, {"HORA.2", type text}}),
#"Renamed Columns" = Table.RenameColumns(#"Changed Type1",{{"HORA.2", "Date"}, {"4 KORA", "Time"}}),
#"Added Index" = Table.AddIndexColumn(#"Renamed Columns", "Index", 1, 1),
#"Added Custom" = Table.AddColumn(#"Added Index", "ForPivot", each Number.RoundUp([Index]/2), type text),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"CHAVE", "HORA.1", "Index"}),
#"Pivoted Column" = Table.Pivot(#"Removed Columns", List.Distinct(#"Removed Columns"[#"4 FL_CTR_ENTRADA_SAIDA"]), "4 FL_CTR_ENTRADA_SAIDA", "Time"),
#"Added Custom1" = Table.AddColumn(#"Pivoted Column", "Duration Hours", each Duration.TotalHours([SAIDA]-[ENTRADA])),
#"Changed Type2" = Table.TransformColumnTypes(#"Added Custom1",{{"Duration Hours", type number}})
in
#"Changed Type2"If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
Hi, j_martinho
Based on your description, I assume that you want to calculate the cumulative value of time difference. I created data to reproduce your scenario. The pbix file is attached in the end.
Table:
You may create two calculated columns and a measure as below.
Calculated column:
DATA = DATE(YEAR('Table'[DATA-HORA]),MONTH('Table'[DATA-HORA]),DAY('Table'[DATA-HORA]))
HORA = TIME(HOUR('Table'[DATA-HORA]),MINUTE('Table'[DATA-HORA]),SECOND('Table'[DATA-HORA]))
Measure:
Balance =
var tab =
ADDCOLUMNS(
ALL('Table'),
"timediff",
var flag = 'Table'[FL_CTR_ENTRADA_SAIDA]
var mat = 'Table'[MAT]
var _datetime = 'Table'[DATA-HORA]
return
IF(
flag = "SAIDA",
DATEDIFF(
CALCULATE(
MAX('Table'[DATA-HORA]),
FILTER(
ALL('Table'),
'Table'[MAT] = mat&&
'Table'[FL_CTR_ENTRADA_SAIDA] = "ENTRADA"&&
'Table'[DATA-HORA]<_datetime
)
),
_datetime,MINUTE
)
)
)
var _mat = SELECTEDVALUE('Table'[MAT])
var _datetime = SELECTEDVALUE('Table'[DATA-HORA])
var result =
SUMX(
FILTER(
tab,
[MAT] = _mat&&
[DATA-HORA]<=_datetime
),
[timediff]
)
return
IF(
ISBLANK(result),
"0h:0m",
INT(result/60)&"h:"&MOD(result,60)&"m"
)
Result:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, v-alq-msft
Thank you. It worked for small samples. At my base I have about 5 million records and the visuals got stuck.
I tried to put the result in a bar graph with Year / Month axis, but it didn't work either.
As a result, I have to display in a table or graph the total hours per month and the total MAT per month.
I am trying to adjust the solution sent, but still learning.