Purchase recency with DirectQuery

Question

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:&nbsp;recency = VAR dateToday = TODAY()VAR daysDiff =DATEDIFF( bc_customer[date_of_last_purchase], dateToday, DAY )VAR res =IF (bc_customer[date_of_last_purchase],SWITCH (TRUE (),daysDiff &lt;= 30, "R5",daysDiff &lt;= 60, "R4",daysDiff &lt;= 120, "R3",daysDiff &lt;= 180, "R2",daysDiff &lt;= 360, "R1","R0"),"R0")RETURNres&nbsp;wheredate_of_last_purchase = MAX(bc_order[order_created_date_time])&nbsp;I can visualize this in a table as follows&nbsp;&nbsp;But I can't view this in a pie chart (see below) such that the pie is divided into the different recency bins which is what I want. In the pie chart, I am unable to drag the recency measure to the Values.&nbsp;&nbsp;Maybe I would need to rewrite this measure as a new column? How would I do this? Or is it something else?

alexisolson · Answer

In order to use it as a category in the visual, it does need to be a calculated column.
&nbsp;
Since it's a calculated column, you can rely on row context rather than needing to take a MAX.
&nbsp;
recency =
VAR daysDiff = DATEDIFF ( bc_order[order_created_date_time], TODAY (), DAY )
VAR res =
    SWITCH (
        TRUE (),
        daysDiff &lt;= 30, "R5",
        daysDiff &lt;= 60, "R4",
        daysDiff &lt;= 120, "R3",
        daysDiff &lt;= 180, "R2",
        daysDiff &lt;= 360, "R1",
        "R0"
    )
RETURN
    res
&nbsp;

rgjiang · Answer

Hi AlexisOlson,&nbsp;thanks so much for your answer. Unfortunately, it saysOLE 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 · Answer

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.
&nbsp;
Try each of these to see what the breaking factor is:
TodayFunction = TODAY ()
&nbsp;
DateDifference = DATEDIFF ( DATE ( 2021, 1, 1 ), DATE ( 2021, 10, 29 ), DAY )
&nbsp;
SwitchFunction =
    SWITCH (
        TRUE (),
        bc_order[order_created_date_time] &lt; DATE ( 2020, 1, 1 ), "Prev Years",
        bc_order[order_created_date_time] &lt; DATE ( 2021, 1, 1 ), "2020",
        "2021"
    )
&nbsp;

rgjiang · Answer

Good to know!&nbsp;I tried them all out and added the SwitchFunction as a new column. It turns out that this causes tis errorOLE DB or ODBC error: [DataSource.Error] ODBC: ERROR [42000] [Microsoft][BigQuery](70) Invalid query: No matching signature for operator &lt; for argument types: DATETIME, TIMESTAMP. Supported signature: ANY &lt; ANY at [9:55].&nbsp;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 · Answer

I'm not quite sure but it sounds like the issue might be comparing date versus datetime.
&nbsp;
Do you get the same error if you use TODAY() or DATEVALUE ( "1/1/2020" ) instead of DATE ( 2020, 1, 1 ) in the SWITCH?

Forum Discussion

Purchase recency with DirectQuery

8 Replies

Related content

RFM - Recency of latest purchase

Microsoft fabric license purchase

DirectQuery Semantic Model

purchasing Fabric capacity F2 or P1

Connection Recency for improved audit and management (Preview)