Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to 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 )
@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
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
hi, @Anonymous
Use ALL instead of ALLEXCEPT
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
Hello @v-lili6-msft
I only need the customers where the first order ever of this customer, contains beachflags and an amount above 750.
@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?
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
@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 )
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |