cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Post Partisan
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
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 )

 

View solution in original post

2 REPLIES 2
Highlighted
Super User II
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 )

 

View solution in original post

Highlighted

Perfect!

Thanks a lot!

Cosmin

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors