Forum Discussion

Shineccx007's avatar
Shineccx007
Helper II
2 years ago
Solved

Count new customer for current year

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.

Data Protection
General Comment
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.