cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## average distinct users per month

I have a table that contains information on users accessing a webpage.

I'm using the following measure to get unique users per day (this is used for the line chart and is correct).

``````count_of_customer =
COUNTROWS (
SUMMARIZE ( 'cr20b_commscampusauditlogs3', 'cr20b_commscampusauditlogs3'[UserId], 'cr20b_commscampusauditlogs3'[CreationTime].[Date])
)``````

I'd also like to include a table that has the monthly average.

I have tried creating a second measure but using [CreationTime].[Month] instad of Day. This is in the table on the right, showing 622 users.  But the value is clearly wrong, as the max unique users per day is around 340.

Can anyone suggest what I have done wrong?

1 ACCEPTED SOLUTION
Resolver II

@nick9one1 ,  Create a Date table and join the Date (Without time) with Date of date table and create a measure given below

Avg of Month =
calculate(
Averagex(Values('Date'[Year Month]) , calculate(
COUNTROWS (
SUMMARIZE ( 'cr20b_commscampusauditlogs3', 'cr20b_commscampusauditlogs3'[UserId], 'Date'[Date])
))), removefilters('date'))

Resolver II

@nick9one1 ,  Create a Date table and join the Date (Without time) with Date of date table and create a measure given below

Avg of Month =
calculate(
Averagex(Values('Date'[Year Month]) , calculate(
COUNTROWS (
SUMMARIZE ( 'cr20b_commscampusauditlogs3', 'cr20b_commscampusauditlogs3'[UserId], 'Date'[Date])
))), removefilters('date'))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors