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