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 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

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

3 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    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

  • Omid_Motamedise's avatar
    Omid_Motamedise
    Icon for Super User rankSuper User
    1 year ago

    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))

     

  • 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.