Count Distinct Numbers from Start to Today

[Schwurblini]

Hi All

I am trying to create a report that shows how many customers have been active up to the current day, filtered by Week

Each and every week, i would need to add the newly joined customer who were active in this week incrementally.

How can i create a report that counts rows distinctively from the Start to the selected Week (I am filtering by Week / Date), i tried to filter with a DATE or WEEKNUM but i couldnt get it to work.

My table looks like this:

month          Month / Year      Week           Customer ID

The Numbers on the right need to be calculated distinctively. I tried with something like this:

All Time Top Nodes = calculate(countrows(DISTINCT(SELECTCOLUMNS('Weekly Sales',"Weeks",'Weekly Sales'[Weeks],"Topnodes",'Weekly Sales'[All Topnodes]))),FILTER('Weekly Sales',WEEKNUM('Weekly Sales'[Month / Year]) >= WEEKNUM(TODAY())-52 && WEEKNUM('Weekly Sales'[Month / Year]) <= WEEKNUM(TODAY())))

But it didnt give me the right summary.

Any advice is welcome 🙂 Best regards and thanks

[Schwurblini]

Wow thanks, im still new to the whole DAX buisness, thank you Tomas and bolfri for the fast and precise help

[bolfri]

Hi,

Your screenshot is showing the data but without column names to understand the context.

1. All the data are in same, single table or are you using some relationship?

2. Where is the "date" field?

3. Are you using calendar table (so we can use time intelligence here)?

Can you describle your problem once again but will different words and using sample data what do you want to achive?

Example (correct me if I am wrong) and put your correct information here.

I have a table of customer activities (and supporting column with week number used from another table)

Customer ID	Date of activity	Support information from calendar table
1	2022-12-19	'22 W51
1	2022-12-03	'22 W48
2	2022-12-16	'22 'W50
3	2022-12-08	'22 W49

I want filter the weeknumber:

• eg. 'W48 that show me that only 1 customer was active in that week (customer ID: 1)
• when I filter 'W49 the measure suppose to show me 2 customers (running total all dates so customer ID: 1 and 3)
• when I filter 'W50 the masure suppose to show me 3 customers (like before but one new: 1,2 and 3)
• when I filter 'W51 the measure suppose to show me also 3 customers (because we need to calculate distint one) by thir last activity

Change everything that's incorrect here and give us some sample (this might be 4-5 example rows) with expected results. 

It will be easier for everyone. 🙂

[TomasAndersson · Accepted solution]

Hi!
Sounds like you want a cumulative (distinct) count? Is this table connected to a Date/Calendar table?
If so it should be enough with

Cumulative Customers = 
CALCULATE(
    DISTINCTCOUNT(Table[Customer]),
        FILTER(ALL(Dates),Dates[Date]<=max(Dates[Date])
    )
)

You'd have to adjust table and columns names according you your data. If any of the columns in your table is a date column (such as [Week/Date]), that should work as well.