cancel
Showing results for
Did you mean:
Highlighted Post Partisan

## DAX filter - 2 condition on same column

Hi

i need to create an expression in which i filter max year month, max year month-1 and max year month-12

sometrhing like this?

MTD =
VAR MaxYM = CALCULATE(MAX(Source[YM]), ALL(Source))
RETURN
CALCULATE(SUM(Source[Sales]), FILTER(Source, Source[YM] = MaxYM - 100 && Source[YM] >= MaxYM - 1))
where YM column is:
201901
201902
201802
201803
201804
201805
201806
201807
201808
201809
201810
201811
201812

could you help me please with the wright expression?
Thanks,
Cosmin
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted Super User II

Hi @cosminc

If I understand correctly you need 3 months: MaxYM, MaxYM-1 and MaxYM-12, i.e. if MaxYM = 201901 you want 201901, 201812 and 201801. Correct? If not please provide  a clearer example with numbers to illustrate what you're after. If correct, try this:

```MTD =
VAR _MaxYM =
CALCULATE ( MAX ( Source[YM] ), ALL ( Source ) )
VAR _MaxYM_12 = _MaxYM - 100
VAR _MaxYM_1 =
IF ( MOD ( _MaxYM, 100 ) = 1, _MaxYM - 100 + 11, _MaxYM - 1 )
VAR _Months = { _MaxYM, _MaxYM_1, _MaxYM_12 }
RETURN
CALCULATE ( SUM ( Source[Sales] ), Source[YM] IN _Months )
```

2 REPLIES 2
Highlighted Super User II

Hi @cosminc

If I understand correctly you need 3 months: MaxYM, MaxYM-1 and MaxYM-12, i.e. if MaxYM = 201901 you want 201901, 201812 and 201801. Correct? If not please provide  a clearer example with numbers to illustrate what you're after. If correct, try this:

```MTD =
VAR _MaxYM =
CALCULATE ( MAX ( Source[YM] ), ALL ( Source ) )
VAR _MaxYM_12 = _MaxYM - 100
VAR _MaxYM_1 =
IF ( MOD ( _MaxYM, 100 ) = 1, _MaxYM - 100 + 11, _MaxYM - 1 )
VAR _Months = { _MaxYM, _MaxYM_1, _MaxYM_12 }
RETURN
CALCULATE ( SUM ( Source[Sales] ), Source[YM] IN _Months )
```

Highlighted Post Partisan

Perfect!

Thanks a lot!

Cosmin  