Forum Discussion

Laveigo1's avatar
Laveigo1
New Member
1 year ago
Solved

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

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 mome...
Need Help
  • BA_Pete's avatar
    BA_Pete
    1 year ago

    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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

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.