Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
bhmiller89
Helper V
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
v-juanli-msft
Community Support
Community Support

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.

11.png

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.

10.png

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

9.png

 

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.

View solution in original post

2 REPLIES 2
v-juanli-msft
Community Support
Community Support

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.

11.png

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.

10.png

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

9.png

 

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.

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.