topic Re: Sales per customer that happened outside their subscription time in DAX Commands and Tips

Sales per customer that happened outside their subscription time

PowerStrang3r — Fri, 06 Jan 2023 11:21:34 GMT

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 🙂

Re: Sales per customer that happened outside their subscription time

johnt75 — Fri, 06 Jan 2023 15:02:37 GMT

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 ) )

Re: Sales per customer that happened outside their subscription time

PowerStrang3r — Fri, 06 Jan 2023 18:01:01 GMT

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% 🙂