DAX filter - 2 condition on same column

[cosminc]

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

[AlB · Accepted solution]

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 )

[cosminc]

Perfect!

Thanks a lot!

Cosmin