Skip to main content
cancel
Showing results for 
Search instead 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

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.


Click here and Go to My LinkedIn Page


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

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