Forum Discussion
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 am using the code below with the intent of using today's date: If the month is greater than 6, use the current year to create the date 7/1/[current year], other subtract one from the current year (7/1/[current minus one). The if statement works in a Custome Column, but how can I nest this if statement in the date range? I am getting an "Expression.SyntaxError: Token Literal expected"
= Table.SelectRows(Source, each [Period] >= #date(2016, 7, 31) and [Period] < if Date.Month(DateTime.LocalNow()) > 6 then #date( Date.Year( DateTime.LocalNow()), 7, 1) else #date(Date.Year( Date.AddYears( DateTime.LocalNow(), -1)), 7, 1))
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
3 Replies
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 SelectDatesI 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
I had to make a minor adjustment:
Table.SelectRows(Source, each [Period] >= Date.From( BeginDate ) and [Period] <= Date.From( EndDate))but otherwise it worked!
