Average logins per user per month/week etc

[DennisNilsson]

Hey, I am new to this and have been banging my head against the wall on this report for a while now so figured I would just ask instead.

We are a SaaS company and have a large database of users, we would therefore like to measure average logins per user per year, month, week, day etc as a KPI.

I really have no idea where to start on this and would really appriciate some best practice tips.

I have a table with a column containing all users and one column containing their login times as dates. If one user has several logins, each login will appear on a new row.

Any tips?

Best regards,

Dennis

[v-juanli-msft · Accepted solution]

Hi

If I understand you correctly, you should be able to follow steps below to get your expected result.

I assume you have a table called "Table1" with columns named "user name" and "date".

1.Use the formulas below to create new calculate columns in the table.

year-month =
YEAR ( [date ] ) & "-"
    & MONTH ( [date ] )

year =
YEAR ( [date ] )

week =
WEEKNUM ( [date ], 1 )

 

2.Use formulas below to create measures.

total logins per user = CALCULATE(COUNT(Table1[user name]),ALLEXCEPT(Table1,Table1[user name]))

number of days = DISTINCTCOUNT(Table1[date ])

number of weeks = DISTINCTCOUNT(Table1[week])

number of month = DISTINCTCOUNT(Table1[year-month])

number of years = DISTINCTCOUNT(Table1[year])

average per day = DIVIDE([total logins per user],[number of days])

average per week = DIVIDE([total logins per user],[number of weeks])

average per month = DIVIDE([total logins per user],[number of month])

average per year = DIVIDE([total logins per user],[number of years])

3.Just show the measures with "user name" column (names of person) on the KPI visual.

Best Regards

Maggie