Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
mhanne
Frequent Visitor

How to get a distinct count for each week and a weekly average of a distinct count

Hello!

I am having trouble figuring out how to sort my data by week. I have the received date and the project ID, but I am unsure how to calculate the week number.  My data consists of individual activities for the projects so I need to use a count of distinct project IDs. 

I am trying to do 2 things:

  • Create a visual of # projects received each week (line graph, bar graph, etc. with the disctinct count on the y axis and week on the x axis) 
  • Calculate the average number of projects received weekly (ex: what is the average number of projects received per week)

My data looks like this

Activity IDProject IDProject Received Date
a12/1/2020
b12/1/2020
c12/1/2020
a23/5/2021
a34/1/2021
b34/1/2021

 

Thank you in advance!! 

1 ACCEPTED SOLUTION
jdbuchanan71
Super User
Super User

@mhanne 

The distinct count of projects is pretty straight forward:

 

 

Projects =DISTINCTCOUNTT ( ProjectsTable[Project ID] )

 

 

The AVG per week will require a calendar table with the year-week in it then you can use a measure like this.

 

 

Avg Weekly Projects = AVERAGEX(
    VALUES(Dates[Year Week]),
    [Projects]
)

 

 

jdbuchanan71_1-1617395596277.png

I have attached my sample file for you to look at.  It also contains theDAXX code to make the calendar table.

 

 

 

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

You should create a Calendar Table and in that write a calculated column formula using the WEEKNUM feature to extract the week number from the Date.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
jdbuchanan71
Super User
Super User

@mhanne 

The distinct count of projects is pretty straight forward:

 

 

Projects =DISTINCTCOUNTT ( ProjectsTable[Project ID] )

 

 

The AVG per week will require a calendar table with the year-week in it then you can use a measure like this.

 

 

Avg Weekly Projects = AVERAGEX(
    VALUES(Dates[Year Week]),
    [Projects]
)

 

 

jdbuchanan71_1-1617395596277.png

I have attached my sample file for you to look at.  It also contains theDAXX code to make the calendar table.

 

 

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.

Top Solution Authors