Count new customer for current year

[Shineccx007]

Hi,

Under value entries we are able to calculate all the historical sales revenue.

Now I wanted to count the new customers this year.

The new customer logic should be [Current year sales] = [Total sales] and I wanted to distinct count the Customer ID. But countrows will count every transaction lines that show on Value Entries.

Anybody got any suggestion?

I also tried distinct count, but the DAX showed error.

[v-junyant-msft · Accepted solution]

Hi @Shineccx007 ,

Here is my sample data:

First use this DAX to create a calculated column:

Flag = 
VAR CurrentYear = YEAR(TODAY())
VAR _Total = 
CALCULATE(
    SUM('Table'[Sales]),
    ALLEXCEPT('Table', 'Table'[Customer ID])
)
VAR _Current =
CALCULATE(
    SUM('Table'[Sales]),
    ALLEXCEPT('Table', 'Table'[Customer ID]),
    YEAR('Table'[Date]) = CurrentYear
)
RETURN
_Total - _Current

Then use this DAX to create a measure:

Count = 
CALCULATE(
    DISTINCTCOUNT('Table'[Customer ID]),
    'Table'[Flag] = 0
)

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PhilipTreacy]

Hi @Shineccx007 

Please check this : How to get your question answered

regards

Phil