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

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 2
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 )
```

Post Partisan

Perfect!

Thanks a lot!

Cosmin

Announcements

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform October Community Highlights

Check out the top community contributors across all of the communities

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