cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bourne2000
Helper IV
Helper IV

How to calculate distinct count weekly?

Hi 

 

I have a below table. Total Customers are 4002 (took distinct count of customer ID). Each customer ID is has multiple login date. I want to calculate how many unique customers (unique customer id) for every week

 

this is the actual data

 

bourne2000_0-1637961337063.png

 

 

I need an output like below

 

bourne2000_1-1637961410406.png

 

 

Just to calculate unique customer ID for every week. I don't want multiple customer ID repeats every week. Most of the customer ID's has multiple same login date for each day and week.

Attached is the sample pbix file https://we.tl/t-vF9b2y6IHg

Can anyone advise?

 

 

1 ACCEPTED SOLUTION
AlexisOlson
Super User
Super User

This sound similar to this prior question:
https://community.powerbi.com/t5/Desktop/Distinct-Count-Measure-in-Table-by-Date-repeating-counts/m-...

 

Distinct New Customers = 
VAR CurrDate = MIN ( sessions[Login Date])
VAR CurrCust = VALUES ( sessions[customer_id] )
VAR PrevCust =
    CALCULATETABLE (
        VALUES ( sessions[customer_id] ),
        ALL ( sessions ),
        sessions[Login Date] < CurrDate
    )
RETURN
    COUNTROWS ( EXCEPT ( CurrCust , PrevCust ) )

 

AlexisOlson_0-1637965089259.png

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

You should ideally have a Calendar Table with a Week number column.  Create a relationship from the Date column of the Data Table to the Date column of the Calendar Table.  To your Table visual, drag Week number from the Calendar Table.  Write this measure

Measure = distinctcount(Data[Customer_ID])

Hope this helps.


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

This sound similar to this prior question:
https://community.powerbi.com/t5/Desktop/Distinct-Count-Measure-in-Table-by-Date-repeating-counts/m-...

 

Distinct New Customers = 
VAR CurrDate = MIN ( sessions[Login Date])
VAR CurrCust = VALUES ( sessions[customer_id] )
VAR PrevCust =
    CALCULATETABLE (
        VALUES ( sessions[customer_id] ),
        ALL ( sessions ),
        sessions[Login Date] < CurrDate
    )
RETURN
    COUNTROWS ( EXCEPT ( CurrCust , PrevCust ) )

 

AlexisOlson_0-1637965089259.png

View solution in original post

Helpful resources

Announcements
2022 Release Wave 1 760x460.png

2022 Release Wave 1 Plan

Power Platform release plan for the 2022 release wave 1 describes all new features releasing from April 2022 through September 2022.

Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors