Forum Discussion
DAX Formula
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.
1 Reply
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())
