Power Query & SQL: Dynamic Date for Where Clause

Hi,

I'm having issues passing a dynamic date field through my SQL statement, in order to filter the data that comes into my report.

I'm using an IF statement within Power Query to determine which of 2 dynamic dates to use. I've got this set up in my Calendar table and it's working fine. But when I try to apply the same login to my SQL statement, if doesn't work.

Here are the date fields and IF statements I'm using:

• CalendarStartDate: this uses 3 number format parameters to create a date (#date(Year,#"Month (Number)",#"Day (Number)")
• HistoricalStartDate: this also uses number format parameters to create a date (Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),-#"Months of Historical Data")
• DynamicStartDate: this is where my IF statement comes in, to decide which of the 2 dates above to use (if HistoricalDataStartDate >= CalendarStartDate then HistoricalDataStartDate else CalendarStartDate)

I've then tried to reference this step in my query, as below:

let
    CalendarStartDate = #date(Year,#"Month (Number)",#"Day (Number)"),
    HistoricalDataStartDate = Date.AddMonths(#date(Date.Year(DateTime.LocalNow()),Date.Month(DateTime.LocalNow()),1),-#"Months of Historical Data"),
    DynamicStartDate = if HistoricalDataStartDate >= CalendarStartDate then HistoricalDataStartDate else CalendarStartDate,
    Source = Value.NativeQuery(GoogleBigQuery.Database([UseStorageApi=true]){[Name=""&#"BigQuery Project Name"&""]}[Data], "SELECT *
    FROM "&#"BigQuery Project Name"&"."&#"BigQuery Database"&".DV360_Metrics_F
    WHERE Day_Dt >= DynamicStartDate
    AND PartnerID = "&#"Partner ID"&"
    AND AdvertiserID "&#"Advertiser ID - In or Not In"&" ("&#"Advertiser ID"&")
    AND FloodLightActivityID <> -1", null, [EnableFolding=true]),
    #"Added Custom" = Table.AddColumn(Source, "Data Source", each "DISPLAY & VIDEO 360", Text.Type)
in
    #"Added Custom"

When I do this, I get the following error:

DataSource.Error: ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: Unrecognized name: DynamicStartDate at [6:21]
Details:
    DataSourceKind=GoogleBigQuery
    DataSourcePath=GoogleBigQuery
    OdbcErrors=[Table]

I'm assuming my date format is incorrect?

I've also tried creating a query to reference in the same statement, as below - the query is Dynamic State Date

let
    Source = Value.NativeQuery(GoogleBigQuery.Database([UseStorageApi=true]){[Name=""&#"BigQuery Project Name"&""]}[Data], "SELECT *
    FROM "&#"BigQuery Project Name"&"."&#"BigQuery Database"&".DV360_Metrics_F
    WHERE Day_Dt >= "&#"Dynamic Start Date"&"
    AND PartnerID = "&#"Partner ID"&"
    AND AdvertiserID "&#"Advertiser ID - In or Not In"&" ("&#"Advertiser ID"&")
    AND FloodLightActivityID <> -1", null, [EnableFolding=true]),
    #"Added Custom" = Table.AddColumn(Source, "Data Source", each "DISPLAY & VIDEO 360", Text.Type)
in
    #"Added Custom"

When I try this, I get the following error, which also looks like it's referencing my format:

Expression.Error: We cannot apply operator & to types Text and Date.
Details:
    Operator=&
    Left=SELECT *
    FROM res-apac-prd-skynet-au.BIReporting_DV360.DV360_Metrics_F
    WHERE Day_Dt >= 
    Right=01/03/2022

I'm a bit stumped on this one. Any ideas on how I can achieve this?

Thanks,

Mark