Forum Discussion

PowerBI_pleb's avatar
PowerBI_pleb
Frequent Visitor
3 years ago
Solved

DAX: Calculate Time Intervals

Hi there,

 

I would like to create a measure to calculate the time interval between rainfall recordings for different rainfall gauges. My gauges have different sampling frequencies. My data follows the structure:

 

gaugedatetimeresults
11/07/2014 12:20:00 AM1
11/07/2014 1:20:00 AM4
11/07/2014 2:20:00 AM3
21/07/2014 12:20:00 AM2
21/07/2014 12:30:00 AM1
21/07/2014 12:40:00 AM4

 

expected intervals - 

location 1: 60mins

location 2: 10mins

 

Can anyone help me with this?

 

Thanks!

Need Help
  • lbendlin's avatar
    lbendlin
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzDXNzIwNFEwNLIyMrAyMFBw9AWJK8XqYKhAVmCCRQGKCcZgBUZ47DDCrsIY3RUYKkxQnBELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [gauge = _t, datetime = _t, results = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"gauge", Int64.Type}, {"datetime", type datetime}, {"results", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"gauge"}, {{"Rows", each _, type table [gauge=nullable number, datetime=nullable datetime, results=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Interval", each [Rows]{1}[datetime]-[Rows]{0}[datetime],type duration)
in
    #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    3 years ago
    let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUTLUNzDXNzIwNFEwNLIyMrAyMFBw9AWJK8XqYKhAVmCCRQGKCcZgBUZ47DDCrsIY3RUYKkxQnBELAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [gauge = _t, datetime = _t, results = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"gauge", Int64.Type}, {"datetime", type datetime}, {"results", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"gauge"}, {{"Rows", each _, type table [gauge=nullable number, datetime=nullable datetime, results=nullable number]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Interval", each [Rows]{1}[datetime]-[Rows]{0}[datetime],type duration)
in
    #"Added Custom"

    How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done".

  • PowerBI_pleb's avatar
    PowerBI_pleb
    Frequent Visitor
    3 years ago

    Thank you lbendlin. This worked, however The table I provided is only an example of my data, and I need a solution to apply to my real data. Is there another way to do this without a json document?

    • lbendlin's avatar
      lbendlin
      Super User
      3 years ago

      Replace the Source step in my code with your actual data source.