Power Query SQL Statement Help >= Prior month start date

[RWNESB]

Hello,

It seems as though this would be simple, however I'm struggling to get this to work.  I have a basic SQL statement and want to add in my where clause a dynamic date.  I would like the date to be the prior month starting date.  Is this possible?  Any suggestions would be appreciated.  Thanks in advance.

[watkinnc · Accepted solution]

The variable can be it's own query, just make a new blank query. Name it StartPriorMonth, and then in the formula bar:

= Text.From(Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1)))

(also took care of making it a text value).  Now you can add it to your query with the ampersand:

WHERE
          Table.DateField>=" & StartPriorMonth)

--Nate

[RWNESB]

Thank you!  Got it working.  I needed to tinker with the variable format a bit, but your original input was exactly what I needed.  You taught me a valuable lesson today, I really appreciate it.  

[watkinnc]

I would do this from the query editor, so that you can make a separate variable to put in your WHERE clause. Nevertheless, assuming your statement needs quotes in the query editor, so here is the record value to use in SQL.Databases:

[Query = "SELECT...WHERE TABLE.DATEFIELD >= "&Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()), -1))]

--Nate

[RWNESB]

Hi @watkinnc ,

Sincerely, thank you for taking the time to respond!  I'm close.  I tried without a variable... Unnsuccessfuly - my problem is that I don't know how to insert a variable within the Advanced Editor.  Pardon my ignorance, just never done this before.  I'm receiving an error "Expression.Error: We cannot apply operator & to types Text and Date."  It makes sense, since I have the string SQL statement that I'm trying to add a date to.  This is where I'm at (simplified):

let
     Source = Odbc.Query("dsn=ABC123", "

     SELECT

          Table.field1, table.field2, ...
     FROM

          Table
     WHERE
          Table.DateField>=" & Date.StartOfMonth(Date.AddMonths(Date.From(DateTime.LocalNow()),-1))) 
in
     Source

How & where do I create the variable?

Thanks again!

[tackytechtom]

Hi @RWNESB,

If I understood correctly, you would like to fetch data from your table where DateField is prior the month of your StartDate.

In normal t-sql, I would probably write it like this:

WHERE
TABLE.DATEFIELD < DATEADD(MONTH,-1, TABLE.StartDate);

I could have misunderstood you, but the syntax might help you anyway.

Good Luck!

/Tom

https://www.tackytech.blog

https://www.instagram.com/tackytechtom