Help with Time difference between multiple row per day and month

[j_martinho]

Hello!

I need to calculate the monthly hourly balance for each "MAT".
In my data source I have "DATA" as date, "HOUR" as hour and "FL_CRT_ENTRADA_SAIDA" as an Input or Output indicator.

For example:

On 16/01 "MAT" 310668 entered 7:48 am, left 12:04 pm, entered 13:04 pm again and left 16:58 pm. Balance from: 08:10 h or 8,1666 hours

Can you help me how to model and calculate?

[mahoneypat · Accepted solution]

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

[v-alq-msft]

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.

[j_martinho]

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.