Forum Discussion

bhmiller89's avatar
bhmiller89
Helper V
7 years ago
Solved

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" 

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

2 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    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's avatar
      ScottGilmore
      New Member
      6 years ago

      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.