Forum Discussion
cosminc
Post Partisan
Post PartisanDAX 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)) RETUR...
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 )
AlB
Community Champion
Community ChampionHi 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 )
cosmincPost Partisan
Post PartisanPerfect!
Thanks a lot!
Cosmin