Forum Discussion

jguercio's avatar
jguercio
Frequent Visitor
4 years ago
Solved

Power Query Rolling Date Range

Hi All,   I am trying to filter a date column in Power Query so only dates from 7/31/2016 to the end of the last fiscal year (i.e. 6/30/2021 for this year, next year it will be 6/30/2022, etc.). I ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    I would make two separate queries for the dates, then just use them as variables. Start a blank query named BeginDate, and in the formula bar, type:

     

    = #date(2016,7,1)

     

    Then another blank query, named EndDate, and in the formula bar, type:

     

    = if Month.From(DateTime.LocalNow()) > 6 then Date.AddMonths(Date.StartOfYear(DateTime.LocalNow()), 6) else Date.AddMonths(Date.AddYears(Date.StartOfYear(DateTime.LocalNow()),  -1), 6)

     

    Now you can go back to your original table, and chance your filter to:

     

    Table.SelectRows(Source, each [Date] >= BeginDate and [Date] <= EndDate)

     

    --Nate

     

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

I would make two separate queries for the dates, then just use them as variables. Start a blank query named BeginDate, and in the formula bar, type:

 

= #date(2016,7,1)

 

Then another blank query, named EndDate, and in the formula bar, type:

 

= if Month.From(DateTime.LocalNow()) > 6 then Date.AddMonths(Date.StartOfYear(DateTime.LocalNow()), 6) else Date.AddMonths(Date.AddYears(Date.StartOfYear(DateTime.LocalNow()),  -1), 6)

 

Now you can go back to your original table, and chance your filter to:

 

Table.SelectRows(Source, each [Date] >= BeginDate and [Date] <= EndDate)

 

--Nate

 

 

  • jguercio's avatar
    jguercio
    Frequent Visitor
    4 years ago

    I had to make a minor adjustment: 

    Table.SelectRows(Source, each [Period] >= Date.From( BeginDate ) and [Period] <= Date.From( EndDate))
     
    but otherwise it worked!