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
AlexisOlson's avatar
AlexisOlson
Super User
4 years ago

In order to use it as a category in the visual, it does need to be a calculated column.

 

Since it's a calculated column, you can rely on row context rather than needing to take a MAX.

 

recency =
VAR daysDiff = DATEDIFF ( bc_order[order_created_date_time], TODAY (), DAY )
VAR res =
    SWITCH (
        TRUE (),
        daysDiff <= 30, "R5",
        daysDiff <= 60, "R4",
        daysDiff <= 120, "R3",
        daysDiff <= 180, "R2",
        daysDiff <= 360, "R1",
        "R0"
    )
RETURN
    res

 

  • rgjiang's avatar
    rgjiang
    Frequent Visitor
    4 years ago

    Hi AlexisOlson,

     

    thanks so much for your answer. Unfortunately, it says

    OLE DB or ODBC error: [Expression.Error] We couldn't fold the expression to the data source. Please try a simpler expression.

    Should I rather split this expression up or is there a simpler way? Sorry if these questions are dumb, I am still very much at the beginning of learning Power BI.

    • AlexisOlson's avatar
      AlexisOlson
      Super User
      4 years ago

      It's not a dumb question, different data sources have different DirectQuery limitations. I tested it with a DirectQuery to a Sql Server and it worked but BigQuery is apparently different.

       

      Try each of these to see what the breaking factor is:

      TodayFunction = TODAY ()

       

      DateDifference = DATEDIFF ( DATE ( 2021, 1, 1 ), DATE ( 2021, 10, 29 ), DAY )

       

      SwitchFunction =
    SWITCH (
        TRUE (),
        bc_order[order_created_date_time] < DATE ( 2020, 1, 1 ), "Prev Years",
        bc_order[order_created_date_time] < DATE ( 2021, 1, 1 ), "2020",
        "2021"
    )

       

      • 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?