Forum Discussion

rgjiang's avatar
rgjiang
Frequent Visitor
4 years ago

Purchase recency with DirectQuery

Hello everyone, I am trying to calculate the purchase recency by months using data from Google BigQuery via DirectQuery. For this, I created this measure:   recency = VAR dateToday = TODAY() ...
Need Help
rgjiang's avatar
rgjiang
Frequent Visitor
4 years ago

Good to know!

 

I tried them all out and added the SwitchFunction as a new column. It turns out that this causes tis error

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 [9:55].

 If I understand this error correctly, this indicates that the data type of bc_order[order_created_date_time] in BigQuery needs to have type TIMESTAMP. Can I avoid this error without modifying the data in BigQuery because I have no write permissions?

AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

I'm not quite sure but it sounds like the issue might be comparing date versus datetime.

 

Do you get the same error if you use TODAY() or DATEVALUE ( "1/1/2020" ) instead of DATE ( 2020, 1, 1 ) in the SWITCH?

  • rgjiang's avatar
    rgjiang
    Frequent Visitor
    4 years ago

    Yes, unfortunately.