Calculating Average Time Logged In (do not add concurrent login time for a user)
Hello, I have a Dax problem that is building off of a previous problem that I was able to solve. I am looking to provide a metric that show the average hours:minutes logged in per user, per day. One of the requirements is since users can have multiple logins per day, and some of these may over lap (since they can be logged in on multiple devices) I do not want to double count these concurrent login time per user. I also want to divide by the total number of unique calendar days that all users are logged in by. Here is what my test data set looks like: For User A, I would Expect to see the following: User ID LogIn LogOut AVG Login Time A 7/10/2022 8:00:00 AM 7/11/2022 4:00:00 PM 16hr:00M A 7/15/2022 8:00:00 AM 7/15/2022 4:00:00 PM 8hr:00M A 8/3/2022 10:00:00 AM 8/3/2022 8:00:00 PM 10hr:00M A 8/3/2022 11:00:00 AM 8/3/2022 12:40:00 PM 1hr:40M A 8/5/2022 8:00:00 AM 8/5/2022 9:00:00 PM 1hr:00M Total 5hr:50M My formula would look like this = (Total Hr:M logged in - any concurrent time) divided by "Unique # of Users" divided by "# of Calendar Days" Total Hours: 36hr:40M Subtract the concurrent logged in time: -1 hr:40M (I do not want to count this since the user was logged in on another instance at this time.) Divided by the number of unique User IDs: 1 Divided by the number of calendar days: 5 (The 10th, 11th, 15th, 3rd, and 5th) So: (36hr:40M - 1hr:40M) / 1 / 5 = 7hr:00M For User B I would expect to see this: User ID LogIn LogOut AVG Login Time B 7/15/2022 8:00:00 AM 7/15/2022 10:00:00 AM 2hr:00M B 8/2/2022 1:00:00 PM 8/2/2022 7:00:00 PM 6hr:00M B 8/2/2022 8:00:00 AM 8/2/2022 12:30:00 PM 4hr:30M B 8/2/2022 9:00:00 AM 8/2/2022 10:00:00 AM 1hr:00M B 8/2/2022 3:00:00 PM 8/2/2022 4:00:00 PM 1hr:00M Total 6hr:15M The math for user B looks like this: Total minutes is 14hr:30m, but on the 2nd, the only time logged in was from 8am to 12:30 pm, and 1:00pm to 7pm. So the total time logged in on the 2nd was 10 hours and 30 minutes. 10hr:30M (on the 2nd) + 2hr:00M (on the 15th) = 12hr:30M. 12hr:30M divided by two days (the 2nd and the 15th = 6hr:15M Divided by unique number of users, which is 1, so this doesn't change the total. So, for everyone's time, I would expect to see Total Time: 51hr:10M Total Time that is non-concurrent: 48hr:00M (35 from user A,
