Forum Discussion
Power Query Rolling Date Range
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
Hello - this is how you can accomplish this. I have added comments in the script with explanations. Basically what I did was declare some variables to represent the min date/max date and steps between, then used those in the filter statement. You will see two variables for currentDate. The first references today and is active in the script. The second is commented and equals a fixed date of 2/20/2021. This second version is for testing purposes. To see the result of a date other than today, comment (add two forward slashes) to be beginning of the first currentDate line and remove the two forward slashes from the beginning of the second currentDate line.
let
// Dates (first of each month) ranging from 10/31/2015 to 12/31/2022.
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("VdJRasMwEEXRveS7YM+4lmbWErL/bTS0cuPzeXlCHISez0fs2xFb7nE+Xl/vjO3Yb5muq8Zv5Zb9qYPte13zVyfbYJtsRTUn/6lD6pA6oM6LWp862C7qhDqhTqgT6oQ6pU6pU+qEWlALakEtqAW1oBbUglpSS2pJLagNtaE21IbaUBtqQ22oLbWlttS+U3O//9VVB9uirjrZBttkK6o5eVGvDNd0XRX3V111sF3UgBpQA2pADaghNaSG1ICaUBNqQk2oCTWhJtSEmlJTakp95+sH", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Period = _t]),
ChangeTypes = Table.TransformColumnTypes(Source,{{"Period", type date}}),
SelectDates = Table.SelectRows(
ChangeTypes,
// declare some variables
let
minDate = #date ( 2016, 7, 31 ),
currentDate = Date.From ( DateTime.FixedLocalNow() ), //--> (8/31/2021) returns dates 7/31/2016 - 6/30/2021
//currentDate = #date ( 2021, 2, 20 ), // --> returns dates 7/31/2016 - 6/30/2020
currentMonth = Date.Month ( currentDate ),
currentYear = Date.Year ( currentDate ),
lastFiscalMonth = 6,
firstFiscalMonth = if lastFiscalMonth = 12 then 1 else lastFiscalMonth + 1,
maxDate =
if currentMonth > lastFiscalMonth
then #date ( currentYear, firstFiscalMonth, 1)
else #date ( currentYear - 1, firstFiscalMonth, 1 )
in
// Compare each value to the variable and include rows whose result = true.
each [Period] >= minDate and [Period] < maxDate
)
in
SelectDates