Power Query Rolling Date Range

[jguercio]

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

[Anonymous · Accepted solution]

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]

I had to make a minor adjustment: 

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

but otherwise it worked!

[jennratten]

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