Forum Discussion

Schwurblini's avatar
Schwurblini
Frequent Visitor
3 years ago
Solved

Count Distinct Numbers from Start to Today

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 wh...
Need Help
  • TomasAndersson's avatar
    TomasAndersson
    3 years ago

    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. 

bolfri's avatar
bolfri
Solution Sage
3 years ago

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 IDDate of activitySupport information from calendar table
12022-12-19'22 W51
12022-12-03'22 W48
22022-12-16'22 'W50
32022-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. 🙂