cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper V

## Relative Date Filtering

I was wondering if anyone knew of a way to almost combine relative date filters.

I have a Line and Stacked column chart of sales data and want to see everything from 12 months ago up until MTD. So, for today, February 21, 2019 I would want to see 3/1/2018-Today which combines full calendar months and MTD.

However, relative date filtering either allows me to see 3/1/2018-1/31/2019 (fully completed months) or 2/22/2018-Today if I use "last 12 months"

1 ACCEPTED SOLUTION
Community Support

Work this out with a workaround as below.

1. create a what-if parameter and get a new table named "Month" and a calcuated column and a measure.

2. create a new date table

`Table 2 = CALENDARAUTO()`

create relationship between "Table2" and your data table, don't create relationship between "Month" table and your data table.

3.create measures

```this month = MONTH(TODAY())

start of this month =
CALCULATE (
MIN ( 'Table 2'[Date] ),
FILTER (
ALL ( 'Table 2' ),
MONTH ( 'Table 2'[Date] ) = [this month]
&& YEAR ( 'Table 2'[Date] ) = YEAR ( TODAY () )
)
)

month diff = DATEDIFF(MAX(Sheet9[date]),[start of this month],MONTH)+1

flag =
IF (
[month diff] > 0
&& [month diff] <= [month Value]
&& [month diff] <> 1,
1,
IF (
[month diff] > 0
&& [month diff] = 1
&& MAX ( Sheet9[date] ) <= TODAY (),
1,
0
)
)
```

If you have any problem, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 REPLIES 2
Community Support

Work this out with a workaround as below.

1. create a what-if parameter and get a new table named "Month" and a calcuated column and a measure.

2. create a new date table

`Table 2 = CALENDARAUTO()`

create relationship between "Table2" and your data table, don't create relationship between "Month" table and your data table.

3.create measures

```this month = MONTH(TODAY())

start of this month =
CALCULATE (
MIN ( 'Table 2'[Date] ),
FILTER (
ALL ( 'Table 2' ),
MONTH ( 'Table 2'[Date] ) = [this month]
&& YEAR ( 'Table 2'[Date] ) = YEAR ( TODAY () )
)
)

month diff = DATEDIFF(MAX(Sheet9[date]),[start of this month],MONTH)+1

flag =
IF (
[month diff] > 0
&& [month diff] <= [month Value]
&& [month diff] <> 1,
1,
IF (
[month diff] > 0
&& [month diff] = 1
&& MAX ( Sheet9[date] ) <= TODAY (),
1,
0
)
)
```

If you have any problem, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

New Member

There is a much easier solution for this:

1. Create a new date table.

``Dates = CALENDARAUTO()``

2. Add a column to that table.

``Trailing Months = 12*(YEAR(TODAY()) - YEAR([Date])) + MONTH(TODAY()) - MONTH([Date])``

3. Add Dates[Trailing Months] as a filter to your visual, page, or all pages.  For example, use "is less than or equal to" = 12 to include the previous 12 calendar months plus the current month.