Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
j_martinho
Helper I
Helper I

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?

 

Screen Shot 06-04-20 at 10.12 AM.PNG

 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

3 REPLIES 3
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


v-alq-msft
Community Support
Community Support

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:

b1.png

 

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:

b2.png

 

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.

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.