Sales per customer that happened outside their subscription time

[PowerStrang3r]

Hello Community!

Im fairly new with DAX, and I am dying to get help with this problem!

I am trying to find out:

1. Distinct count of the customers who purchased Product A, OUTSIDE their subscription time.
2. Count of the purchases (Product A) they made OUTSIDE their subscription time 

My data model is very simple:

Customer table (relation 1 - * w/ CustomerID) Sales table (relation * - 1 w/Date) Date Table
Customer table (relation 1 - * w/ CustomerID) Subscriptions table 

One customer can have two different subscriptions on and off (SubscriptionA, SubscriptionB), which have starting date and ending date. Some are still active and they dont have an ending date.
At any time they can also make purchases (Product A) and Im interested to find out only those which have been made outside these subscription times. Note, that some customers have never been subscribers, only bought Product A. I need these as well.

Hope I was clear with me explanation and someone has time to help with this 🙂

[johnt75 · Accepted solution]

You could create a measure like 

Purchases outside subscriptions =
VAR CustWithSub =
    FILTER (
        VALUES ( 'Customer'[Customer ID] ),
        NOT ISEMPTY ( RELATEDTABLE ( 'Subscription' ) )
    )
VAR CustWithoutSub =
    FILTER (
        VALUES ( 'Customer'[Customer ID] ),
        ISEMPTY ( RELATEDTABLE ( 'Subscription' ) )
    )
VAR CustWithDates =
    GENERATE (
        CustWithSub,
        VAR CustID = 'Customer'[Customer ID]
        VAR SubAStart =
            LOOKUPVALUE (
                'Subscription'[Start date],
                'Subscription'[Customer ID], CustID,
                'Subscription'[Type], "Subscription A",
                MAX ( 'Date'[Date] )
            )
        VAR SubAEnd =
            LOOKUPVALUE (
                'Subscription'[End date],
                'Subscription'[Customer ID], CustID,
                'Subscription'[Type], "Subscription A"
            )
        VAR SubBStart =
            LOOKUPVALUE (
                'Subscription'[Start date],
                'Subscription'[Customer ID], CustID,
                'Subscription'[Type], "Subscription B",
                MAX ( 'Date'[Date] )
            )
        VAR SubBEnd =
            LOOKUPVALUE (
                'Subscription'[End date],
                'Subscription'[Customer ID], CustID,
                'Subscription'[Type], "Subscription B"
            )
        VAR SubADates =
            DATESBETWEEN ( 'Date'[Date], SubAStart, SubAEnd )
        VAR SubBDates =
            DATESBETWEEN ( 'Date'[Date], SubBStart, SubBEnd )
        RETURN
            UNION (
                EXCEPT ( VALUES ( 'Date'[Date] ), SubADates ),
                EXCEPT ( VALUES ( 'Date'[Date] ), SubBDates )
            )
    )
VAR NumPurchasesWithoutSub =
    CALCULATE ( COUNTROWS ( 'Sales' ), CustWithoutSub )
VAR NumPurchasesWithSub =
    CALCULATE (
        COUNTROWS ( 'Sales' ),
        TREATAS ( CustWithDates, 'Customer'[Customer ID], 'Date'[Date] )
    )
RETURN
    NumPurchasesWithoutSub + NumPurchasesWithSub

to give you the number of purchases. And then create a measure like

Num customers =
COUNTROWS (
    FILTER (
        VALUES ( 'Customer'[Customer ID] ),
        [Purchases outside subscriptions] > 1
    )
)

[PowerStrang3r]

@johnt75   Thank you SO SO MUCH for taking your time and helping me with this!!!

Let me try this on during the weekend and come back to you if it worked 100% 🙂