Forum Discussion

vazfelipe's avatar
vazfelipe
Frequent Visitor
8 years ago
Solved

Calculating time difference in a vertical column

Hi guys,

I have two columns, one with repetead dates and other one with the hour the transaction occured.

Do there's a way to calculate the time duration in certain day?

For example, here below I have many transactions in the day twelve, which occured in a range of time.

How many hours do I have in the day twelve?

 

  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi vazfelipe,

     

    To group the Date Column and SUM the hours in that range of date, you can firstly group by the "Order Day" column and get the Min and Max "Order Hour" in the range of the date.

     

     

    Then you should be able to use the formula(M) below to add a custom column to SUM the hours in that range of date.

    =Duration.Hours([MaxHour]-[MinHour])

     

    Following is the M query of all steps for your reference:

    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDRX0lEytjIwtDIwUHD0VYrVQZExsjIxAckEYMgY45QxQZUx0jeGyRgaWJkY4ZAyRJKKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Day" = _t, #"Order Hour" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Day", type date}, {"Order Hour", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Day"}, {{"MinHour", each List.Min([Order Hour]), type time}, {"MaxHour", each List.Max([Order Hour]), type time}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hours", each Duration.Hours([MaxHour]-[MinHour]))
in
    #"Added Custom"

     

    Regards

7 Replies

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        8 years ago

        Hi vazfelipe,

         

        To group the Date Column and SUM the hours in that range of date, you can firstly group by the "Order Day" column and get the Min and Max "Order Hour" in the range of the date.

         

         

        Then you should be able to use the formula(M) below to add a custom column to SUM the hours in that range of date.

        =Duration.Hours([MaxHour]-[MinHour])

         

        Following is the M query of all steps for your reference:

        let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtQ31DcyMDRX0lEytjIwtDIwUHD0VYrVQZExsjIxAckEYMgY45QxQZUx0jeGyRgaWJkY4ZAyRJKKBQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Order Day" = _t, #"Order Hour" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Order Day", type date}, {"Order Hour", type time}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Order Day"}, {{"MinHour", each List.Min([Order Hour]), type time}, {"MaxHour", each List.Max([Order Hour]), type time}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Hours", each Duration.Hours([MaxHour]-[MinHour]))
in
    #"Added Custom"

         

        Regards

  • drad2211's avatar
    drad2211
    Frequent Visitor
    6 years ago

    Hi

     

    I have a somewhat similar question. I have a table that has the following columns (ID, Time, Action) and I want to add a new column (I believe by making a DAX equation) that can show the time difference (in mins) for specific ID and the 'In' 'Out' Action. I would like the time difference to be shown only on the 'Out' row. The data file is titled 'Data' and the table looks like: 

     

    ID                                       Time                             Action

    1                            08:00:00 05/05/2015                   In

    1                            11:00:00 05/05/2015                  Out

    2                            12:00:00 05/05/2015                   In

    1                            13:00:00 05/05/2015                   In

    1                            15:00:00 05/05/2015                  Out

    2                            17:00:00 05/05/2015                  Out

    .                                           .                                      .

    .                                           .                                      .

     

    And I would like the table to look like:

     

    ID                                       Time                             Action                     Time Diff

    1                            08:00:00 05/05/2015                   In

    1                            11:00:00 05/05/2015                  Out                           180

    2                            12:00:00 05/05/2015                   In

    1                            13:00:00 05/05/2015                   In

    1                            15:00:00 05/05/2015                  Out                           120

    2                            17:00:00 05/05/2015                  Out                           420

    .                                           .                                      .

    .                                           .                                      .

     

     

    Any advice would be helpful. 

    Thank you