Filter dates for current and previous 2 months

[Anonymous]

Hello,

I have the following table loaded in Power Query and i would like to keep only the rows where the Start date is in the current month and previous 2 months, based on today's date. So, the results should be dynamic and only have rows with Start date in February, March and April 2022. Any help is much appreciated!

Name	Start date	End date
Lizui	18/11/2021	08/12/2021
Laufenburg	24/11/2021	16/12/2021
Tegalpapak	20/12/2021	12/12/2021
Ar Rabiyah	29/11/2021	09/12/2021
Gangarampur	17/12/2021	29/12/2021
Bellegarde	20/12/2021	26/12/2021
Luntas	25/12/2021	29/12/2021
Frei Paulo	17/12/2021	29/12/2021
Seedorf	19/12/2021	25/12/2021
Bellegarde	26/12/2021	31/12/2021
Gangarampur	26/12/2021	31/12/2021
Cosamaloapan de Carpio	27/12/2021	02/01/2022
Luntas	03/01/2022	09/01/2022
Zagrodno	07/01/2022	26/01/2022
Bellegarde	20/12/2021	10/01/2022
Lizui	22/12/2021	11/01/2022
Laufenburg	19/12/2021	09/01/2022
Luntas	06/01/2022	27/01/2022
Tegalpapak	10/01/2022	16/01/2022
Seedorf	16/01/2022	22/01/2022
Frei Paulo	27/01/2022	16/02/2022
Ar Rabiyah	23/01/2022	30/01/2022
Gangarampur	28/01/2022	17/02/2022
Bellegarde	04/02/2022	16/02/2022
Gangarampur	31/01/2022	06/02/2022
Cosamaloapan de Carpio	26/01/2022	16/02/2022
Luntas	31/01/2022	21/02/2022
Zagrodno	31/01/2022	16/02/2022
Lizui	27/01/2022	15/02/2022
Laufenburg	30/11/2021	30/11/2021
Tegalpapak	30/11/2021	30/11/2021
Ar Rabiyah	09/02/2022	20/02/2022
Gangarampur	16/02/2022	09/03/2022
Bellegarde	10/02/2022	27/02/2022
Luntas	19/02/2022	10/03/2022
Frei Paulo	20/02/2022	25/02/2022
Seedorf	21/02/2022	27/02/2022
Bellegarde	21/02/2022	14/03/2022
Gangarampur	21/02/2022	13/03/2022
Cosamaloapan de Carpio	14/02/2022	20/02/2022
Luntas	04/03/2022	22/03/2022
Zagrodno	28/02/2022	06/03/2022
Bellegarde	06/03/2022	12/03/2022
Lizui	09/03/2022	30/03/2022
Laufenburg	06/03/2022	13/03/2022
Luntas	09/03/2022	30/03/2022
Tegalpapak	14/03/2022	30/03/2022
Seedorf	11/03/2022	16/03/2022
Frei Paulo	18/03/2022	18/03/2022
Ar Rabiyah	14/03/2022	20/03/2022
Gangarampur	14/03/2022	04/04/2022
Bellegarde	09/03/2022	30/03/2022
Gangarampur	30/11/2021	30/11/2021
Cosamaloapan de Carpio	28/03/2022	08/04/2022
Luntas	23/03/2022	30/03/2022
Zagrodno	17/03/2022	27/03/2022
Lizui	21/03/2022	25/03/2022
Laufenburg	01/04/2022	21/04/2022
Tegalpapak	28/03/2022	15/04/2022
Ar Rabiyah	30/03/2022	20/04/2022
Gangarampur	12/04/2022	03/05/2022
Bellegarde	10/04/2022	16/04/2022
Luntas	18/04/2022	27/04/2022
Frei Paulo	01/01/2022	01/01/2022

[ronrsnfld]

If you mean starting 3 months ago from today, then:

    #"Filtered Rows" = Table.SelectRows(#"Previous Step", each [Start date] >= 
        Date.AddMonths(Date.From(DateTime.LocalNow()),-2) and [Start date] <= #date(2022, 4, 19))

If you mean something else, please be more specific.

[Anonymous]

Hi @ronrsnfld Thanks for your reply! I am referring to 3 whole calendar months, starting from 2 months ago (as from first of February) and until the end of the current month. But your code does not include a dynamic way to find the current month end date.

[ronrsnfld · Accepted solution]

Easy fix now that you have clarified your requirements:

   #"Filtered Rows" = Table.SelectRows(#"Changed Type", each 
        [Start date] >= Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-2))       
        and [Start date] <= Date.EndOfMonth(Date.From(DateTime.LocalNow())))

or, to reproduce table and filtering:

et
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hZY7b9swFIX/iuE5gPmyZI9tgGbJELSdGmRgYMUVqkgGUw3try9pNeI5si8z0vx0H+cekn58XN+3f8d2fbPWu43WG6OMjgsVF2ZaPN1Exo8vTf88hmPcMw5AXRH4vTn67uRP/lcC1bwXQUPgp7D66p/bP/5nAveYek/gne+PPvjX0xhSlBpCGiY/N10X04dDs8xtuMj7sf/t39LvWznal9C0qwc/dsMHab81zWEIL4naI7UtFFcBaHWh3xJ5O7z5V98NUe1+dWhWtz6c2lStwWqV2aiztoZ7V3b+/aw5Qj/8MQyHPsVSNWCxGsRkwbXipP8tZgwymhm0GCm5KC53UGFpNUHkQ6jmbFgEYXoUjlUjM5h6Ec5kkG2NEluWZDHmHYasKSSprNy8d5GbQ1qN82VSdk4ltzbrTpGNJgicQ9gy1rshSMotM2iIqF6+IWBxMesSSMNJrpqVjOaVlYTap8+sMBytMGR9XT2NedMXVrIZRWNtsmtB/4ucfD4R1I7yLsxIpCVSdI52spz5xOa00xGzV52TTkMWvJIFh73zA4Pgu8VgYNMhtILFOBi3nTsoROM7x8kg3Dkac1ayGeLjDOCOQLI15TWqMGYi02ScpHKh58WdUzh88p2DrcX/HVhHfqmtXAM4J12dufv6qiEMap7OlWgIPdcyfeWEWVMH6RZzwnCg8Gk4ThyOgdzpqd4W7hyHDrqmnt5hJzVBZDNFjwY80E//AA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Name = _t, #"Start date" = _t, #"End date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{
        {"Name", type text}, 
        {"Start date", type date}, 
        {"End date", type date}},"en-150"),
 
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each 
        [Start date] >= Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-2))       
        and [Start date] <= Date.EndOfMonth(Date.From(DateTime.LocalNow())))
in
    #"Filtered Rows"