Relative Date Filtering

[bhmiller89]

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" 

[v-juanli-msft · Accepted solution]

Hi @bhmiller89 

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.

[ScottGilmore]

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.