Forum Discussion
Distinc count Active Customer from Historical Table by Month
Hi everyone
I've been looking around the community for something that might provide an answer to my problem. I have a Table hitorical Customer which display as below and I want to distinc Count Customer_Code by each month.
I tried create measure as below but didnot work
= TOTALYTD(COUNTDISTINC(CUSTOMER_HISTORY[CUSTOMER_CODE])),'DATE'[Date])
Thanks for your attentions. And appriciate to guide me to solve.
Hi,
I am not sure how your datemodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.I hope the below can provide some ideas on how to create a solution for your datamodel.
Customer Count measure: = VAR _t = SUMMARIZE ( FILTER ( CUSTOMER_HISTORY, CUSTOMER_HISTORY[FROM_DATE] <= MAX ( 'Date'[Date] ) && OR ( CUSTOMER_HISTORY[TO_DATE] >= MIN ( 'Date'[Date] ), CUSTOMER_HISTORY[TO_DATE] = BLANK () ) ), CUSTOMER_HISTORY[CUSTOMER_CODE] ) RETURN COUNTROWS ( _t )
2 Replies
- Jihwan_Kim
Super User
Hi,
I am not sure how your datemodel looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.I hope the below can provide some ideas on how to create a solution for your datamodel.
Customer Count measure: = VAR _t = SUMMARIZE ( FILTER ( CUSTOMER_HISTORY, CUSTOMER_HISTORY[FROM_DATE] <= MAX ( 'Date'[Date] ) && OR ( CUSTOMER_HISTORY[TO_DATE] >= MIN ( 'Date'[Date] ), CUSTOMER_HISTORY[TO_DATE] = BLANK () ) ), CUSTOMER_HISTORY[CUSTOMER_CODE] ) RETURN COUNTROWS ( _t )