Slicer not working on measures

[Anonymous]

Hi Community! 

I have put together a table that compares new and returning customers (see screenshot).

When I apply a slicer, however, the numbers in Return Customers turn negative (see below). 

My DAX pattern for new customers is: 

New Customers =

VAR FirstPurchaseCustomers =

ADDCOLUMNS (

ALL ( 'Sales (f)'[client_id] ),

"DateOfFirstBuy", CALCULATE ( MIN ( 'Sales (f)'[date] ), ALLEXCEPT ( 'Sales (f)', 'Sales (f)'[client_id] ) )

)

VAR NewCustomers =

FILTER (

FirstPurchaseCustomers,

CONTAINS ( VALUES ( 'Sales (f)'[date] ), 'Sales (f)'[date], [DateOfFirstBuy] )

)

RETURN

COUNTROWS ( NewCustomers )

My DAX pattern for return customers is: 

Returning Customers = [Customers] - [New Customers]

Where [Customers] is a distinct count of the client id.

Any help would be highly appreciated!

Thank you! 

[JarroVGIT · Accepted solution]

Your context doesnt' take into account a possible filter on Location column. Change your first var to something like this. It is hard to write DAX for me without intellisense so be aware of any typo's 🙂

VAR FirstPurchaseCustomers =
    ADDCOLUMNS (
        SUMMARIZE(ALLEXCEPT('Sales (f)', 'Sales (f)'[Location]), [client_id], "DateOffFirstBuy",  MIN ( 'Sales (f)'[date] ))

Hope this helps you.

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂

[amitchandak]

In the sales tables, I create the following. this is a bit costly operation

First Sales Date = MINX(filter(sales,Sales[Sales Date]<EARLIER(Sales[Sales Date]) && Sales[Customer Id]= EARLIER(Sales[Customer Id])),Sales[Sales Date])

It will be null for the first sales.

Total Customer = distinctcount(sales[customer id])
New Customer = CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),ISBLANK(Sales[First Sales Date]))
Repeat Customer = CALCULATE(DISTINCTCOUNT(Sales[Customer Id]),not(ISBLANK(Sales[First Sales Date])))

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601