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

Perfect!

Thanks a lot!

Cosmin

Helpful resources

Announcements
November Update

Check it Out!

Click here to read more about the November 2020 Updates!

Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

secondImage

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

secondImage

Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors