Help with Time difference between multiple row per day and month

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?

Need Help

Show and Tell

Tips and Tricks

mahoneypat
6 years ago
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

3 Replies

v-alq-msft

Community Support

6 years ago

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.

Help with Time difference between multiple row per day and month.pbix82 KB

j_martinho
Helper I
6 years ago
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.

mahoneypat
Microsoft Employee
6 years ago
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

Forum Discussion

Help with Time difference between multiple row per day and month

3 Replies

Related content

Date difference with multiple criteria

How to avoid people creating multiple times the same table in different workspaces

Different schema into different destination

Multiple PDFs Different Tables

MULTIPLE CARD