How to find number of customers in a SCD2 type table

[a4apple]

Hello Everyone,

I have an SCD2 type table like below.

I have a date table and time table as well. I want to find out the number of distinct customers when the user selects a date or show number of users by date so on.. Does this model help me do that? It's a typical SCD Type 2 dimension I am using for my Data.

Thanks for your help in advance.

[tringuyenminh92]

Firstly, I create Dates table for your date filters: 

Modeling Tabl -> New Table -> 

Dates = CALENDARAUTO()

In your data table, I create 2 Calculated Measure:

Selected = FIRSTNONBLANK(values(Dates[Date]),1)

(To get current selected value)

And Distinct count customer by CustomerID

No. of cus = CALCULATE(DISTINCTCOUNT(Sheet1[CustomerID]),filter(Sheet1,Sheet1[StartDate]<= [Selected] && [Selected] < Sheet1[EndDate]  )) 

 If this works for you please accept it as solution and also give KUDOS.
 

[a4apple]

@CheenuSing, @tringuyenminh92 guys thank you so much. Most of the work is done with your dax statement. I want to know, what if I want to filter the data using an other table as well along with date? How can I acheieve that?

For example, lets say there are events table, I want to know how many customers are with eventtype = 'A' on that day?

like filter from another dimension other than date as well and not Facts when it has the CustomerID but not the surrogate Key (CustomerKey).

I hope my question is understandable.

or else,

Lets say I have a dimension which is also SCD 2 about customers and their membership types.

If I select Membership Type = 'Platinum' and Date = '2016-11-25', I want to know the number of customers with membership type "Platinum" on the day of "2016/11/25". Is this possible by two dimension tables? or do we need them as Facts?

Please help.

[tringuyenminh92 · Accepted solution]

Hi @a4apple,

I believe above formula has done what you describe about event or membership type if there is no mismatch with my understand.

Just re-check 2 things:

• For events table scenario, make sure there is relationship between foreign key column(in Customer table) with Primary key of Event table 
• For membership type, if this is one of column of customer table, it done.

You just drag&drop Slicer control for event/membership type to check what we are doing.

[CheenuSing]

Hi @a4apple

Create measure as follows

ActiveCustomers =                  Calculate(
                                                                             DISTINCTCOUNT(CustomerSCD[CustomerId]),
                                                                                         FILTER(  CustomerSCD,
                                                                                                  COUNTROWS( FILTER(VALUES('Calendar'[Date]),
                                                                                                                           CustomerSCD[StartDate] <= 'Calendar'[Date] &&
                                                                                                                              CustomerSCD[EndDate] >= 'Calendar'[Date] ) )
                                                                                                                                      > 0 )
                                                                       )

This measure does the following:
Calculate the distinct count of CustomerSCD[CustomerId]  for those rows in the CustomerSCD table that has more than 0 rows in the Calendar table where CustomerSCD[StartDate] <= 'Calendar'[Date]  and CustomerSCD[EndDate] >= 'Calendar'[Date].

If this works for you please accept it as solution and also give KUDOS.

Cheers

CheenuSing