Show customers based on historical sales

[Anonymous]

Hi all! 

I need to filter my customer data. I have sales data for a couple of months. But I only want to see data for customers that purchased in month 1. 

So if the customer purchased something in month 1, I want to se all his purchases. If the customer did not purchase in month 1 i do not want to include the customer. In the below example I do not want to include date for customer 3. 

Any suggestions on how I can achieve this? All help is highly appreciated!!! 

Christer

[v-xulin-mstf · Accepted solution]

Hi @Anonymous,

Try meausre as:

Measure = 
var _isblank=
SUMX(
    FILTER(
        ALL(Table1),
        Table1[Customer]=MAX(Table1[Customer])
    ),
    IF(
        ISBLANK(Table1[Sales]),
        1,
        0
    )
)
var _sum=
SUMX(
    FILTER(
        ALL(Table1),
        Table1[Customer]=MAX(Table1[Customer]) && MONTH(Table1[Date])>=9 && MONTH(Table1[Date])<=11
        ),
        Table1[Sales])
return
IF(
    _isblank=0 && _sum>7500,
    1,
    0
 )

Here is the output:

The pbix is attached, please try it.

If you still have some question, please don't hesitate to let me known.‌‌

Best Regards,

Link

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution. Really appreciate!

[amitchandak]

@Anonymous , based on data I see, Try a measure like

measure  =

var _tab = except(distinct(Table[customer]), filter(distinct(Table[customer]), isblank([sales date])))

return

calculate(sum(Table[sales]) , filter( Table, Table[Customer] in _tab))

[Anonymous]

Hi @amitchandak 

Thank you for your reply. I think I oversimplified the task a bit. 

I have sales data for a couple of months. I have a criteria for a further analysis I need to apply to the customer selection. 

If sales were above 7500 in september, october or november I want to include the customer. However, if there are no sales in any of these months, but sales above 7500 in later months, I do not want to include this customer. 

So in this example, I do not want to include the above customers at all, but i do want to include the bottom two customers. 

Christer