I've failed at this for so long I'm finally going to ask for other sets of eyes to confirm my finding or to out-think me and solve it. I don't think it can be done but it would be so powerful for some many of my clients if we could sort it out.
The scene
A fact table with a row per Customer Interaction including a Date. Among the types of interactions are one-off purchases. Also included are the start date of customers' subscriptions and the end date of customers' subscriptions.
There's also a dimension with customers' subscription history details including start dates and end dates. When a customer interaction fact row is directly relatable one of their subscriptions, it is joined to that subscription's details in that dimensional table.
We also use date dimensions in which we plan to add integer versions of the date to facilitate formulas around first/last purchase that can be responsive to filters. (e.g. can't use formula filters in combination with DDIFF().)
Users want to create a formula counting one-off purchase that occurred during a customer's subscription without altering the data model. Were they a subscriber at the time of one-off purchase? Worth noting that subscriptions have data security applied such that users may be permitted to see some types of subscriptions and not others.
Example - fact rows
- Customer subscription start date 2023-02-01
- Customer subscription end date 2024-01-31
- Customer one-off purchase 2024-06-12
- Customer subscription start date 2024-07-01
- Customer one-off purchase 2024-08-12
- Customer subscription end date 2025-06-30
Filter widget to Interaction Type = One-off Purchases
SUM ( [Customer ID] ,
SUM ( [Order ID] ,
IF (
MIN ( [Order Date Integer] ) >= {a subscription start date}
MIN ( [Order Date Integer] ) >= {the same subscriptions end date}
, 1 , 0 )
)
)
...or...
SUM ( [Customer ID] ,
( SUM ( [Subscription ID] ,
IF (
MIN ( [Subscription Start Date Integer] ) <= {date of the One-off Purchase}
MIN ( [Subscription End Date Integer] ) >= {date of the One-off Purchase}
, 1 , 0 )
) , [Interaction Type = Subscription] )
)
I can't come up with a clever way to retain the scope of the one-off purchase when breaking out of one-off purchase to look at subscriptions, and vice versa.
