Last 3 Months Filter

[Datagulf]

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.

[Anonymous · Accepted solution]

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

[Jihwan_Kim]

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.