cancel
Showing results for
Did you mean:
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

I need an output like below

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

1 ACCEPTED SOLUTION
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 ) )``````

2 REPLIES 2
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
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 ) )``````

Announcements

#### 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.