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.
Hello,
I need help on writing DAX formulas for the following criterias.
I have a set of data as follows.
Customer # | Product | Date | Qty |
1493719 | A | 1/1/2014 | 1 |
1493719 | A | 2/1/2014 | 1 |
1493719 | B | 5/1/2015 | 1 |
1493719 | A | 6/1/2016 | 1 |
1493719 | C | 7/1/2017 | 1 |
1493719 | C | 8/1/2018 | 1 |
1863579 | A | 2/1/2014 | 1 |
1863579 | C | 5/1/2015 | 1 |
1863579 | A | 6/1/2016 | 1 |
1761625 | B | 5/1/2015 | 1 |
1761625 | B | 7/1/2017 | 1 |
1761625 | A | 8/1/2018 | 1 |
Case 1) I would like to create a measure to indicate 1 if a customer bought "A" product within last two years.
Case 2) I would like to create a measure to indicate 1 if a customer did not buy any product within last two years and bought product "A" during the first three years but the third year must be "A"
* Last two years would be 2018, 2017 in this data
* First three years would be 2014, 2015, 2016 in this data
If any additional information is needed, please let me know.
Thank you for your help.
A in Last 2 years = CALCULATE(DATEDIFF(MAX(Table1[Date]),TODAY(),YEAR),Table1[Product]="A")>0 A not in two years but in last 3 = IF(NOT([A in Last 2 years]),CALCULATE(DATEDIFF(MAX(Table1[Date]),TODAY(),YEAR)<=3,Table1[Product]="A"),FALSE())
Note, I probably would not explicitly write the measure to filter for Product = A but rather use a visual filters to limit the product
In Last 2 years = DATEDIFF(MAX(Table1[Date]),TODAY(),YEAR)>0 Not in 2 Years but in last 3 = IF(NOT([In Last 2 years]),DATEDIFF(MAX(Table1[Date]),TODAY(),YEAR)<=3,FALSE())
//If you really need to explicit measure then write additional measures A in Last 2 years = CALCULATE([In Last 2 years],Table1[Product]="A")>0 A not in two years but in last 3 = IF(NOT([A in Last 2 years]),CALCULATE([Not in 2 Years but in last 3],Table1[Product]="A"),FALSE())
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |