Forum Discussion

cosminc's avatar
cosminc
Icon for Post Partisan rankPost Partisan
7 years ago
Solved

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
Need Help
  • AlB's avatar
    AlB
    7 years ago

    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

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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 )