Sum Days Logged in Based on user and date

[NB689]

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!

[vapid128]

rankindex =

var _rankLogin =RANKX(FILTER('Table','Table'[User ID]=EARLIER('Table'[User ID])),[Login],,ASC)

var _rankLogOut = RANKX(FILTER('Table','Table'[User ID]=EARLIER('Table'[User ID])),[Logout])

return _rankLogin+DIVIDE(1,_rankLogOut)+RAND()/1000

Rank = RANKX(FILTER('Table','Table'[User ID]=EARLIER('Table'[User ID])),[rankindex],,ASC)

NextLogin = LOOKUPVALUE('Table'[Login],'Table'[User ID],'Table'[User ID],'Table'[Rank],'Table'[Rank]+1)

daysLogin =

var _loginReal =IF([NextLogin]>0, MIN([Logout],[NextLogin]),[Logout])

return _loginReal-[Login]

That doesnt works when your data like:

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

Normally Login Logout data shouldn't happned this case.

[tamerj1 · Accepted 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
    )
)

[NB689]

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!

[tamerj1]

Hi @NB689 

please try

Days = SUMX ( 
        Sheet1,
        DATEDIFF ( Sheet1[Login], Sheet1[Logout], Day ) + 1
    )

[NB689]

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.

[tamerj1]

@NB689 

Please clarify further perhaps with some screenshots 

[amitchandak]

@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])

[NB689]

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.