cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## 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.

1 ACCEPTED SOLUTION
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 )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
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 )
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Frequent Visitor

Dear Jihwan_Kim

By refer to your solution, I worked with me, thanks

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors