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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Datagulf
Responsive Resident
Responsive Resident

Last 3 Months Filter

I have a Date Table and would like to Filter the Last 3 Months. This means if we are in the Month of December, there should be November, October and September all crumbled together as one item on a filter. How is this possible? Thanks.

1 ACCEPTED SOLUTION
v-shex-msft
Community Support
Community Support

Hi @Datagulf,

You can create an unconnected date table and use it as source slicer. Then you can write a measure formula to compare selected date and current date if it is including in the three months and use it on your visual to filter records.

Applying a measure filter in Power BI - SQLBI

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR selected =
    MAX ( NewTable[Date] )
RETURN
    IF (
        currDate
            >= DATE ( YEAR ( selected ), MONTH ( selected ) - 3, DAY ( selected ) )
            && currDate <= selected,
        "Y",
        "N"
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

View solution in original post

2 REPLIES 2
v-shex-msft
Community Support
Community Support

Hi @Datagulf,

You can create an unconnected date table and use it as source slicer. Then you can write a measure formula to compare selected date and current date if it is including in the three months and use it on your visual to filter records.

Applying a measure filter in Power BI - SQLBI

formula =
VAR currDate =
    MAX ( Table[Date] )
VAR selected =
    MAX ( NewTable[Date] )
RETURN
    IF (
        currDate
            >= DATE ( YEAR ( selected ), MONTH ( selected ) - 3, DAY ( selected ) )
            && currDate <= selected,
        "Y",
        "N"
    )

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your datamodel looks like, but one of ways to solve this is using DATESINPERIOD DAX function.

 

DATESINPERIOD – DAX Guide

 

For the StartDate parameter in the function, try using EOMONTH( TODAY(), -1) which gives end date of previous month.

 

I hope this helps.

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.

Top Solution Authors