Sum First logins

[Anonymous]

Hi All,

I'm trying to calculate the number of first logins by date. Here's a simplified version of my dataset:

User ID     Login Date

123           01/01/18

123           02/01/18

123           06/01/18

456           02/01/18

456           08/01/18

789           01/01/18

789           04/01/18

I have used  this formula, calculate(MIN(PA[Date]),ALLEXCEPT(PA,PA[user_Id])), to pull out the first login date by user ID, but I would like to calculate the sum of first logins by date to create a table like so:

Date              New Logins

01/01/18       2

02/01/18       1

Any advice wuld be appreciated.

Thanks,

Chris

[Yemeng_Hu · Accepted solution]

Hi Chris, 

Here you go:

First_logIn_Date = Calculate(
                                   Min(Login Date), 
                                   AllExcept (PA, PA[User ID])
                                   )

New_Customer_count = Calculate( DistinctCount(PA[Customer ID], 
                                                         Filter( PA,
                                                         PA[Login_Date] = PA[First_login_Date]))

First_login_Date is a calculated column, and New_customer_count is a measure. 

Thanks,

Yemeng

[v-yulgu-msft]

Hi @Anonymous,

Based on above table, you can create a calculated column to get your desired result.

Result table = SUMMARIZE(PA,PA[First login date],"CountID",DISTINCTCOUNT(PA[UserID]))

Alternatively, you could directly use a table visual to display data like below:

Best regards,

Yuliana Gu

[Anonymous]

Thanks Yuliana. Does the first login need to be a specific format? It's not allowing me to add  PA[First Login date] to the formula. When I try adding to a table, it only shows one date. It's only when I add userID to the tabel that i get the full list of first login dates.

Thanks,

Chris

[v-yulgu-msft]

Hi @Anonymous,

PA[First Login date] has to be a calculated column rather than a measure.

Regards,

Yuliana Gu