cancel
Showing results for
Did you mean:
Regular Visitor

## 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

Solution Sage

```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())

```

Announcements