Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Sum First logins

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

Need Help
  • Yemeng_Hu's avatar
    Yemeng_Hu
    8 years ago

    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

4 Replies

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi Anonymous,

         

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

         

        Regards,

        Yuliana Gu

  • Yemeng_Hu's avatar
    Yemeng_Hu
    Frequent Visitor
    8 years ago

    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