Calculating whether new customers or Existing Customers

[Shivkanyabyale]

 I have a coumn invoice date based on taht i am creating new customer or existing customer 
NewCustomer =
VAR FirstPurchaseDate =
CALCULATE(
MIN('Sales Register'[Invoice Date]),
ALLEXCEPT('Sales Register', 'Sales Register'[Client Code])
)
RETURN
IF(
'Sales Register'[Invoice Date] = FirstPurchaseDate,
"New Customer",
"Existing Customer"
)

 i want to show total customers,new customers and existing cutomers in same scorecard based on new customer column filter
 but i am not getting correct count as total customers shoud be new+ existing 

This is because for seleted period say month for example we get total customers which is distinct count of client code which is correct and new customers which is also correct but when we select existing customers customers are repiting as same customer previosusly had been as new customer in seleted period so that has alredy been counted still we are counting thus not getting correct result 
 i also tried existing customers= total-new still i am not getting correct result
also my data is item level so same invoice has repited multple times depends on number of items 
if anyone know how to solve this please let me know 

[v-yiruan-msft]

Hi @Shivkanyabyale ,

It seems like you are trying to create is a calculated column, it will not change according to the user interaction(slicer, filter, column selections etc.) in the report as the value of a calculated column is computed during data refresh and uses the current row as a context... Please review the following links about the difference of calculated column and measure...

Calculated Columns and Measures in DAX

Calculated Columns vs Measures

You can provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples. Later we can create a measure as below to replace the original calculated column to get the expected result. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

Best Regards

[Shivkanyabyale]

I couldn't share sample data here but my data is item level data for say each invoice I can have multiple entries because same invoice has multiple items so against each customer
I am creating column which will give me first purchase date of that customer then using that column I am checking if invoice date = first purchase date then customer is new else existing i did this because i want to create a filter of new customer and existing customer based on that i want to see which customers are new in other visual .
If i select some period then i am getting customer only related to that period based on customer status column this is working good until here
problem now is when i shown total count of customers in scorecard i want to show total customers if something sleeted then then base on that count of new or existing customers but say i have selected a month then in that month my total customers are 500 without customer status selection if I have selected new customers i get count 200 because new customers are 200 this is good but
when i select existing customers then i get say 400 this is because it contains existing customers this is possible that same client can be new or existing of selected month because that customer have purchased multiple time so same customer got counted in new customer also and existing customer also i want to show count of new when new customers is selected but when existing is selected then total-new so i can get correct count and my total customers will be = New+EXISTING

[v-yiruan-msft]

Hi @Shivkanyabyale ,

Thanks for your reply. Could you please provide some raw data in your tables(exclude sensitive data) with Text format and your expected result with backend logic and special examples? It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

[Fowmy]

@Shivkanyabyale 

could you share your DAX measures ?

[Shivkanyabyale]

Customers Updated =

VAR AllCustomers =

    DISTINCTCOUNT('Sales Register'[Client Code])

VAR NewCustomerCount =

    CALCULATE(

        DISTINCTCOUNT('Sales Register'[Client Code]),

        'Sales Register'[New Customer] = "New Customer"

    )

RETURN

    SWITCH(

        TRUE(),

        SELECTEDVALUE('Sales Register'[New Customer]) = "New Customer", NewCustomerCount,

        SELECTEDVALUE('Sales Register'[New Customer]) = "Existing Customer", AllCustomers - NewCustomerCount,

        AllCustomers

    )
This is my measure 

New Customer =

VAR FirstPurchaseDate =

    CALCULATE(

        MIN('Sales Register'[Invoice Date]),

        ALLEXCEPT('Sales Register', 'Sales Register'[Client Code])

    )

RETURN

IF(

    'Sales Register'[Invoice Date] = FirstPurchaseDate,

    "New Customer",

    "Existing Customer"

)
this is my column for New customer or existining customer status