Can't filter Data column for the last 6 months

[mdor]

Hello,

I have a task to reconnect a table with the Date column to BigQuery in my existing report. The Date column has a DATETIME type.

My Power Query code looks like this for filtering values for the last 6 months:

let
    Source = GoogleBigQuery.Database(null),
    BQDatabase = Source{[Name=bq_project_name]}[Data],
    BQSchema = BQDatabase{[Name=bq_db_name,Kind="Schema"]}[Data],
    BQView = BQSchema{[Name=bq_view,Kind="Table"]}[Data],
    Filtered_Last6Mo = Table.SelectRows(BQView, each Date.IsInPreviousNMonths([DateKey], 6)),
in
    Filtered_Last6Mo

When I do apply these queries, I can see the table with data on the Power Query side. After I click on "Close & Apply", I got the following error on the Power BI side in a few minutes:

OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: No matching signature for operator >= for argument types: DATETIME, TIMESTAMP. Supported signature: ANY >= ANY at [23:8].

It used to work when I used MS SQL as a data source for the table.

Can anyone help me with this issue?

Thanks in advance!

[mdor]

Hello,

I have a task to reconnect a table with the Date column to BigQuery in my existing report. The Date column has a DATETIME type.

My Power Query code looks like this for filtering values for the last 6 monthes:

let
    Source = GoogleBigQuery.Database(null),
    BQDatabase = Source{[Name=bq_project_name]}[Data],
    BQSchema = BQDatabase{[Name=bq_db_name,Kind="Schema"]}[Data],
    BQView = BQSchema{[Name=bq_view,Kind="Table"]}[Data],
    Filtered_Last6Mo = Table.SelectRows(BQView, each Date.IsInPreviousNMonths([DateKey], 6)),
in
    Filtered_Last6Mo

When I do apply these queries, I can see the table with data on the Power Query side. After I click on "Close & Apply", I got the following error on the Power BI side in a few minutes:

OLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [Microsoft][BigQuery] (70) Invalid query: No matching signature for operator >= for argument types: DATETIME, TIMESTAMP. Supported signature: ANY >= ANY at [23:8].

 Can anyone help me with this issue?

Thanks in advance!

[amitchandak]

@mdor , Not very clear.

You can have 6 month of data

example

Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX(Sales[Sales Date]),-6,MONTH))
Rolling 6 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-6,MONTH))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Please provide your feedback comments and advice for new videos
Tutorial Series Dax Vs SQL Direct Query PBI Tips
Appreciate your Kudos.

[mdor]

It used to used work before when I used the MS SQL server. But with the BigQuery, filtering stopped working for me. Also, it's not a solution for me because it should be done on the Power Query side, not with DAX.

[edhans · Accepted solution]

I don't have BigQuery to play with here, but if the field from BIgQuery is DateTime, you should try converting it to Date only, then use the Date.IsInPreviousNMonths. That won't fold once you do it I am 99% certian.

The other way you could try it without switching the data type is this:

1. Create a new blank query
2. Use this formula: = Date.StartOfMonth(Date.AddMonths(DateTime.LocalNow(), -6))
3. Rename the query to varStartDate
4. Change your Table.SelectRows() above to this: Table.SelectRows(BQView, each [DateKey]<= varStartDate)

See if that will run. That will fold as Power Query will pass a constant of the value for varStartDate, which right now is April 1, 2020 at 12:00am.