Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Can anyone advise?
Solved! Go to Solution.
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 ) )
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.
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 ) )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
99 | |
80 | |
70 | |
59 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |