Power Query Editor Date Filter 2 quarters prior but not the prior quarter

[Laveigo1]

Hi

I am trying to filter my data to only show data from 2 quarters prior from today (e.g. April to June) but not the prior quarter's data (July to September). Is there a way to do so? At the moment, I can see that there is a filter for "Prior Quarter" and "Date.IsInPreviousNQuarters".

= Table.SelectRows(#"xxx", each Date.IsInPreviousNQuarters([date],2)) allows me to filter the data from April to September, but I do not want to include the prior quarter data (July to September). I would like to do this dynamically, so that come next quarter, the prior quarter and 2 quarter prior will adjust automatically. Does anyone know how to do so? Your help is much appreciated. 

Thanks

[Anonymous]

Hi @Laveigo1 ,

Thanks for all the replies!
And @Laveigo1 , here is my sample data:

You can try this M code:

let
    Source = Table.FromRecords({
        [Date=#date(2024,11,30), Value=11],
        [Date=#date(2024,10,31), Value=10],
        [Date=#date(2024,9,30), Value=9],
        [Date=#date(2024,8,31), Value=8],
        [Date=#date(2024,7,31), Value=7],
        [Date=#date(2024,6,30), Value=6],
        [Date=#date(2024,5,31), Value=5],
        [Date=#date(2024,4,30), Value=4],
        [Date=#date(2024,3,31), Value=3],
        [Date=#date(2024,2,29), Value=2],
        [Date=#date(2024,1,31), Value=1]
    }),
    CurrentDate = DateTime.LocalNow(),
    PreviousQuarterStart = Date.From(Date.AddQuarters(Date.StartOfQuarter(CurrentDate), -1)),
    PreviousQuarterEnd = Date.From(Date.EndOfQuarter(PreviousQuarterStart)),
    PreviousTwoQuartersStart = Date.From(Date.AddQuarters(Date.StartOfQuarter(CurrentDate), -2)),
    PreviousTwoQuartersEnd = Date.From(Date.EndOfQuarter(PreviousTwoQuartersStart)),
    FilteredData = Table.SelectRows(Source, each Date.From([Date]) >= PreviousTwoQuartersStart and Date.From([Date]) <= PreviousTwoQuartersEnd and Date.From([Date]) < PreviousQuarterStart)
in
    FilteredData

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Omid_Motamedise]

you can add another condition and neglect value in the previous quarter by rewriting your formula as:

= Table.SelectRows(#"xxx", each Date.IsInPreviousNQuarters([date],2) and not Date.IsInPreviousNQuarters([date],1))

[BA_Pete · Accepted solution]

Hi @Laveigo1 ,

You can try using your current filter, but also excluding the prior quarter at the same time, something like this:

Table.SelectRows(
    PreviousStepName,
    each Date.IsInPreviousNQuarters([date], 2)
        and not Date.IsInPreviousNQuarters([date], 1)
)

Pete