First sales date of a customer

[Anonymous]

Hi All, 

I need the first salesdate of every customer in my data.

This first sales must contain the category code "beachflags" and have at least an amount (salesamount) of 750. 

How can i make this in DAX with the variables 

custnr

catogrycode

salesamount 

salesdate

I hope someone have a solution

[OwenAuger]

@Anonymous 

You can create a calculated column like this (adjust column names as required):

First Sales Date =
CALCULATE (
    MIN ( Sales[salesdate] ),
    ALLEXCEPT ( Sales, Sales[custnr] ),
    Sales[catogrycode] = "beachflags",
    Sales[salesamount] >= 750
)

This formula retains just the customer filter from the current row, then also applies the category and amount filters.

Regards,

Owen

[Anonymous]

@OwenAuger 

ok thanks! 

Do you also have a solution for the following: 

all the customers where the very first salesorder contains beachflags and an amount > 750

[v-lili6-msft]

hi, @Anonymous 

Use ALL instead of ALLEXCEPT

Spoiler

Measure =
CALCULATE (
    MIN ( Sales[salesdate] ),
    ALL ( Sales ),
    Sales[catogrycode] = "beachflags",
    Sales[salesamount] >= 750
)

 If not your case, please share your sample data and expected output.

Best Regards,
Lin

[Anonymous]

Hello @v-lili6-msft 

I only need the customers where the first order ever of this customer, contains beachflags and an amount above 750.

[OwenAuger]

@Anonymous  - I see there have been a few posts on this.

Just clarifying the requirements.

Do you want to identify all customers whose first ever order was "beachflags" and >=750, and then include all rows relating to those customers? 

For example, in the table you posted, you want to include all rows for Custno=4 since that customer meets the condition?

Also, do you want to create a calculated column flagging the relevant rows, or create a filter on the fly to use within a measure?

[Anonymous]

Whose first beachflags order was >= 750. 

And i want those customers in a matrix, doesn't matter if it works with a measure or calculated column

[Nishantjain · Accepted solution]

@Anonymous 

Assuming you are trying to create a custom column and not a measure, this probably should work. 

First Sales Date =
// Return true if it is a first order
Sales[salesdate] = 
CALCULATE (
    MIN ( Sales[salesdate] ),
    ALL ( Sales),
    Sales[custnr]  = Earlier(Sales[custnr] ),
    Sales[catogrycode] = "beachflags",
    Sales[salesamount] >= 750
)