The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
I'm looking to create a measure that will show the sum number of days, that user(s) have logged in.
Here is a sample of the data from a table called Sheet1:
User ID | Login | Logout | Days Logged In |
A | 7/18/2022 8:00 AM | 7/21/2022 4:00 PM | 4 |
A | 7/19/2022 8:00 AM | 7/21/2022 4:00 PM | 3 |
A | 7/21/2022 8:00 AM | 7/23/2022 4:00 PM | 3 |
B | 7/18/2022 8:00 AM | 7/21/2022 4:00 PM | 4 |
B | 7/18/2022 8:00 AM | 7/20/2022 4:00 PM | 3 |
C | 7/18/2022 8:00 AM | 7/19/2022 4:00 PM | 2 |
C | 7/21/2022 8:00 AM | 7/22/2022 4:00 PM | 1 |
C | 7/17/2022 8:00 AM | 7/18/2022 4:00 PM | 1 |
Total | 15 |
The sumation per row is fairly straight forward. This is the logout time minus the login time, and I add a 1 so that I get the total number of calendar dates.
The sumation Total for all would be counted like this.
For user A, they were logged in for 6 days (The 18th, 19th, 20th, 21st, 22nd, & 23rd).
For user B, they were logged in for 4 days (The 18th, 19th, 20th, & 21st).
For user C, they were logged in for 5 days (The 17th, 18th, 19th, 21st & 22nd.)
So the total value for the column that I would like to calcuate would be 15 days(6 days/4 days, 5 days.)
I do have a current measure created, but it does not sum everything correctly. It looks like this:
Days = SUMX (
Sheet1,
DATEDIFF ( Sheet1[Login], Sheet1[Logout], Day )
) +1
Any help on this issue will be much appreciated. Thank you!
Solved! Go to Solution.
Hi @NB689
If I correctly understand correctly then use
Days =
SUMX (
VALUES ( Sheet1[User ID] ),
CALCULATE (
DATEDIFF ( MIN ( Sheet1[Login] ), MAX ( Sheet1[Logout] ), DAY ) + 1
)
)
User ID | Login | Logout | Days Logged In |
A | 7/18/2022 8:00 AM | 7/22/2022 4:00 PM | 5 |
A | 7/19/2022 8:00 AM | 7/21/2022 4:00 PM | 3 |
Hi @NB689
If I correctly understand correctly then use
Days =
SUMX (
VALUES ( Sheet1[User ID] ),
CALCULATE (
DATEDIFF ( MIN ( Sheet1[Login] ), MAX ( Sheet1[Logout] ), DAY ) + 1
)
)
I entered the exact test data into Power BI just to be sure it wasn't something with my larger data set throwing it off. So here I added a column for the first measure and the most recent one you sent with MIN and MAX.
The results that I intend to see with this new column would look like:
User ID | Days (expected outcome) |
A | 4 |
A | 3 |
A | 3 |
B | 3 |
B | 4 |
C | 2 |
C | 2 |
C | 2 |
Total | 15 |
Why do I want to see 15? I only want to count each day one time per user. So for this view:
I would want to see the following:
User ID | Days (expected outcome) |
A | 4 |
A | 3 |
A | 3 |
Total | 6 |
I would want to see 6 because the only days that this user was actually logged in were on the 18th, 19th, 20th, 21st, 22nd, & 23rd. With the other measure, I would be double counting some of the days.
So for user B, I would want to see a total of 4 days (The 18th, 19th, 20th, & 21st)
For user C I would want to see 5 days (The 17th, 18th, 19th, 21st & 22nd.) This one might make the DAX tricky, since it skips a day in the series.
Let me know if you're not sure of where I got some of these numbers I can clarify. Thanks for your help!
This gets me a total of 21, so a sum total of all days, but I need to deal with setting up measure to not count the same days multiple times.
@NB689 , You have to correct logout first
Use this logout column
New Logout=
Var _max = minx(filter(Table, [User ID] = earlier([User ID]) && [Login] > earlier([Login])),[Login])
return
if([Logout] >_max, _max, [Logout])
This is probably a syntax thing that I'm looking over, but Power Pivot doesn't like any of my column names. I do have a date column for Login/Logout, and I created a measure that I can use within this measure, whichever way I am supposed to use it.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
58 | |
32 | |
20 | |
17 | |
15 |
User | Count |
---|---|
56 | |
31 | |
30 | |
24 | |
21 |