Show customer id distinct count by month

[Anonymous]

Hi all,

I tried to look for an answer for this but I couldn't find exact answer.

I have this large dataset, which has customer orders by rows and one customer usually has multiple orders per one date and they make orders multiple times a year. I need a distinct count of how many different customers per month have made an order. I have a bar chart visualisation as customer order date for x-axis and customer id for y-axis. If I use the normal distinct count in visualisation (sort by distinct count), it shows one customer id once in the timeline but if I put count (all) it shows the customer id multiple times. 

Here is a very simplified version of the dataset. The customer id can show multiple rows in the same day, but the same customer can order also next month something and I need the customer id to show in that month also.

customer id		order date
01190105428930		18.1.2023
01190105428930		18.1.2023
01190205428182		13.1.2023
01190205428182		13.1.2023
01190205429293		11.1.2023
01190205429293		11.1.2023
01190205429293		11.1.2023
01190205429293		11.1.2023
01190205429293		11.1.2023
01190205429293		11.1.2023
01190205429293		11.1.2023
01190205429293		11.1.2023
01190205429293		11.1.2023
01190405426378		18.1.2023
01190505428266		25.1.2023
01190505428902		4.1.2023
01190505428902		4.1.2023
01190505428902		4.1.2023
01190505428902		4.1.2023
01194905395211		12.9.2022
01195105393451		18.10.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195600061243		9.11.2022
01195704282464		8.3.2023
01195705103550		30.11.2022
01195800039012		2.2.2023
01195800050816		19.7.2022

Basically I need a measure to show distinct customer id's by month. Customer can make an order both in May and September and be shown in the bar chart in both months.

What would be the best measure to visualize this? Thank you!

[Sahir_Maharaj]

Hello @Anonymous,

1. To get a distinct count of customers who made an order each month:

Distinct Customer Count = DISTINCTCOUNT('Table'[customer id])

2. To ensure that the same customer ID is not counted more than once in a single month:

Distinct Customer Count per Month = 
DISTINCTCOUNT(
    FILTER(
        'Table',
        NOT(
            COUNTROWS(
                FILTER(
                    'Table',
                    'Table'[order date] < EARLIER('Table'[order date]) &&
                    'Table'[customer id] = EARLIER('Table'[customer id])
                )
            ) > 0
        )
    ),
    'Table'[customer id]
)

Let me know if you might require further assistance.

[Anonymous]

Hello,

thank you for your answer! I tried this but it says "Too many arguments were passed to DISTINCTCOUNT. The maximum argument count for the function is 1". Is there a way to fix this? Thank you!