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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Keith011
Helper III
Helper III

how to calculate the actual view time when there's overlap of time stamps

Hi there!


this was originally solved by @jbwtp. However, now there's a slight change to my case scenario but i have no idea how to tweak the M code. @jbwtp solution works perfect but if someone is able to tweak it , it'll be easier else i would need help for a whole new solution

Refer to : Solved: Re: Calculate the actual time duration when there ... - Microsoft Power BI Community

(note : the only difference between the case in this current post vs the link above , is the schedule table. Now, i have a schedule table by several webinar name. In the previous case, i only had 1 webinar with 2 rows : 1st row is start time and 2nd row is end time.)

 

My goal : calculate the view time for each attendees

 

this is my attendance table

webinar nameemailjoin timeleave timeduration Aduration B
webinar 1john123@gmail.com1:00:00 PM1:30:00 PM3030
webinar 1john123@gmail.com1:15:00 PM1:30:00 PM150
webinar 1john123@gmail.com1:30:00 PM1:45:00 PM1515
webinar 1john123@gmail.com1:50:00 PM2:00:00 PM1010
webinar 1john123@gmail.com1:10:00 PM1:53:00 PM430
webinar 2peter9@gmail.com5:00:00 PM5:03:00 PM33
webinar 2peter9@gmail.com5:30:00 PM5:35:00 PM55
webinar 2peter9@gmail.com5:35:00 PM5:45:00 PM1010
webinar 2peter9@gmail.com5:15:00 PM5:20:00 PM55
webinar 2peter9@gmail.com5:30:00 PM6:00:00 PM308
webinar 2peter9@gmail.com5:53:00 PM6:00:00 PM77

 

there's a few things to note about this table.

  1. This table is just an example as the actual data is way more than this
  2. The join time is not "properly sorted". You can see there's alot of overlapping time by the same person
  3. duration A is given by the system (which is totally incorrect)
  4. duration B is what i need (to calculate 'actual view time' for each person)

 

this is my schedule table

webinar namestart timeend time
webinar 11:00:00 PM2:00:00 PM
webinar 25:00:00 PM6:00:00 PM

 

In order to calculate the 'actual view duration', it needs to refer to the schedule to get the start / end time info and then only start to calculate

 

 

1 REPLY 1
jbwtp
Memorable Member
Memorable Member

Hi Keith,

 

This is the updated version of the query. Could you please test and confirm that this is what you need?

let
    Tasks = 
        let t = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("nVnJctNAEP0VSmcO0oxkqnxjTyCBmDXgOMHZg3G2C79PbFlMv96kSRUcTM2jt9evZ1rTafH37Pjqen7/pCqeFs8f/lbjsnz482Rvd/0jpB+hLGZPNUC1PnO7bH80/380Q86HdL7SAWFcJyfiuGySRzoAzsSNR208eghx49IGEHtjrqmFhmaskS69kFklFqK08FICaAzSwkuSVmlBieGVtEADEudfZ55/86jzDaksEE8HhA13NoDolvmt9IiYU6r2ltMfcmpYCNQC+qdbgDgjRdeWhUC5TbJqAhrgNgHoLsGZCEXRLdQ0MQ39MTIBjQ4w6oAdRgNqdAvNuE4WRmBOB8CZZ7TdlLRucS5B2ZWgtySXSFqjDLoFRErvIS411CW/0lut22CBpNU4D0JcuzFvy4arXY+2yTiQWVWS9C7XwvtcDdh5HCAYWVVc2uGFhhgUldmRwkfqoMyG3Uwp3uUhIFelRx+kgcoVyg9SKKPbDR+VQrtCuScBvoU96ZJPpT1eWwCYFmJp1E3GMJEx+HexiVI4d4ZOeCah0gqVJvwMqJLhEpyBy5giGpNCXMb8tE74fwoDS6HGClCDmELKLEDGhJvwMzCwjCyNaNAjiAGDDg//9ClHllaAzzlcSoBgSGuwLAwUvg6AlzzKRMMlbDKYXjpg8MV+BfiSoZTpPLS0zYwV4Cs34N0bOsBgWVoBvinMMC9LCVAZVDIsMOEjFmrdwuA7aAdgL7jgFvq7Mh5cC/u5WdqXdfD750euSz84lypnPCRAY1BD0YCf0iV7JiYAXE3st0ACwHig13Zxfprr0TQ3SdMi45WYAFTqYCTqACQ00ESPAa7OMIyMoGHdUAOVdJdqdMm+UiYA7QD6Q5GlqTgDr1gJODhQSj0EEQxyVDJPG0Rt0M9CcHp4hF0DIr7Y/bG4QURa7uBKzQYBD63GJfkaUVNEA1PPRsAj3HwiE0QGR9YI9gyv3M5YI0a0HKOebp1JVrkDe8ZpBHKgBDErxCOZFka3wGYXlTRp4ZCH0HclOJQsp+ySLh3lWjjiwhqclesK8EuWwV5XJgBYoPSzADBZKBV1AFutVW7bzXOz1ALgteVqx1xyL7h1mxfi7VS6PTrP5d6cCxgojfToWNbZvzad5Cb1RJDHec11AHYx63eJd/QACwPf7R2AKT59CUnAaaaKnXKyhZ6RcspL5e0GOwA+x2BWiPNnMgJ7850A2Dyu0p/xMEPPbaAF0MUVPHJ0QMS9gL2q6ABYWghItwCTuYFhIi2c5+rqeW6hz/mZ6KyZE6A2+k0p3IWMwb/4XUgJoKJpAUBYS1f2LhWX3Bv4pWC089mlAzC22uv+BKgN6VZa+io3hhYA1wY6HS0AjNzYC8B9YE/hfssY7K0uARjUUCr9W8bgPkgXuTrWAoIhxYpoLAq5NnbbYVGwz4KxZ8C1ABg/Za8FthCNrlK2gGAIn3Ue9qH+vGoBdKMJ40cHZD01FuszJQDc+fNHMsPfuP5RpNgl9zJX7JdKN7hBLzlZ+17US6WjXWYsC/5JGvrNAsAyJLhZulay5M6fa3GGBiQ9uuEG+q6sNzzz+AHPsgAj1F8A3XBdCT1Lh1tlOLgScCt10r8s3RZicWJ/aloB7hRldReod7J/KjfoO0lWqjLi/H1uR9+LjoGVi3qeP0F7DbBFub/RveftBt++FJ1sAbAZDAMA0J/2R/8O4K+iZv8A", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [webinar = _t, Email = _t, startedAt = _t, finishedAt = _t, #"timeSpent (minutes)" = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Table.Combine({t/*, t, t, t, t, t, t, t, */}),{{"Email", type text}, {"startedAt", type time}, {"finishedAt", type time}, {"timeSpent (minutes)", Int64.Type}})
        in #"Changed Type",
    Webinars = 
        let
            Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WKk9NysxLLFIwVNJRMrQyMAAihQBfIMcIwYnVQagzAkqZIqszQ1IXCwA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"webinar name" = _t, #"start time" = _t, #"end time" = _t]),
            #"Changed Type" = Table.TransformColumnTypes(Source,{{"webinar name", type text}, {"start time", type time}, {"end time", type time}})
        in
            #"Changed Type",
    #"Merged Queries" = Table.NestedJoin(Tasks, {"webinar"}, Webinars, {"webinar name"}, "Webinars", JoinKind.LeftOuter),
    Main = Table.ExpandTableColumn(#"Merged Queries", "Webinars", {"start time", "end time"}, {"start time", "end time"}),

    #"Added latestStartedAt" = Table.AddColumn(Main, "latestStartedAt", each List.Max({[startedAt],[start time]}), type time),
    #"Added earliestFinishedAt" = Table.AddColumn(#"Added latestStartedAt", "earliestFinishedAt", each List.Min({[finishedAt], [end time]}),type time),
    #"Filtered Rows" = Table.SelectRows(#"Added earliestFinishedAt", each [latestStartedAt] < [earliestFinishedAt]),
    fCalculate = (t as table) =>
        let 
            m = Table.Buffer(t),
            fProcess = (a, n)=>
                let 
                    previousFinishedAt = List.Last(a)[earliestFinishedAt],
                    currentStartedAt = n[latestStartedAt],
                    currentFinishedAt = n[earliestFinishedAt],
                    actualFinish = List.Last(a)[actualFinish],
                    actualStart = List.Max({actualFinish, currentStartedAt}),
                    outputRecord = [#"ActualtimeSpent (minutes)" = List.Max({0, Duration.TotalMinutes(currentFinishedAt-actualStart)}), actualFinish = List.Max({actualFinish, currentFinishedAt})]
                in outputRecord,

            process = List.Skip(List.Accumulate(Table.ToRecords(m), {[actualFinish = m{0}[startedAt], #"finishedAt" = m{0}[startedAt]]}, (a, n)=> a & { n & fProcess(a, n) }))
        in process,
    Group = Table.Group(#"Filtered Rows", {"webinar", "Email"}, {{"Data", fCalculate}}),
    Expand = Table.FromRecords(List.Combine(Group[Data]), Value.Type(Table.AddColumn(Main, "ActualtimeSpent (minutes)", each null,type number))),
    #"Removed Other Columns" = Table.SelectColumns(Expand,{"webinar", "Email", "startedAt", "finishedAt", "timeSpent (minutes)", "ActualtimeSpent (minutes)"}),
    Output = Table.Combine({#"Removed Other Columns", Table.RemoveColumns(Table.SelectRows(#"Added earliestFinishedAt", each not ([latestStartedAt] < [earliestFinishedAt])), {"latestStartedAt", "earliestFinishedAt", "start time", "end time"})})
in
    Output

 

Kind regards,

John

Helpful resources

Announcements
PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors