Every time an account is activated for a new customer, the activation date is registered in the Customer Master table. Then, the sales related to the activated accounts are registered in the Sales DataTable, BUT not all the activated accounts ended up with a sale registered.
That means that there are accounts in the Customer Master table with no sales related to them, mainly because, in the end, the customer changed his mind and decided not to purchase from the company, although it can be any other reason. How can I establish a control over those accounts that have been activated but that have no sales registered? I would like to have a count of the accounts, but also, being able to identify the customer’s names.
I saw the video and also I have been reviewing some other videos of your Ultimate Beginners Guide to DAX and Advanced Data Transformations & Modeling.
After reviewing them, I decided to create a lookup table for what I need to achieve. In this lookup table, I want to have the Customer ID, Activation date of the accounts and total sales. Not sure if this is the best approach, though.
The thing is that I’m struggling with the “Activation Date” column. For some reason, I’m not getting all the activation dates that are listed in the other table. Just those who has sales registered.
what I need help now is finding, based on the account activation date, how many accounts from the table “Customer Data” have NEVER had a sales registered in the “sales” table, when a period is selected. Which means that the account was activated but never registered any sale. I’m not sure about the DAX formula here
Nice one, model looks much better. Hopefully you can see the difference as well.
Ok around your question. How do you want to showcase this.
What will be the context of the calculation? Will is be customer, dates or something else. Do you want to take a guess at the formula and then showcase where you get to. Then we can work on it from there.
The context of the calculation will be the number of customers over a period of time, like in this example:
I tried the following formulas but are not giving me the correct number …
Active Customer = //Accounts with "A" status in the Customer Data Table
RELATEDTABLE( 'Customer Data'),
'Customer Data'[Account Status] = "A"))
Zero Purchase =
CROSSFILTER( 'Calendar'[Date], Sales[SALES DATE], NONE ))
You see here there’s no relationship between these two (and there shouldn’t be).
This is also why setting your model out with the waterfall technique really helps because you would be able to see immediately there isn’t a relationship.
Always highly recommend going through the below course. The formulas you’re using aren’t really appropriate for something like this.
I personally rarely ever need to use RELATEDTABLE and CROSSFILTER functions.
For the active customers measure.
It should read more like this.
CALCULATE( DISCOUNTCOUNT( Customer ID column from sales table ),
FILTER( Customer Date Table, Account Status column = “A” )
Zero purchase should then be.
CALCULATE( Active Customer measure,
FILTER( Customer Data table, Sales Amount = 0 )
Maybe you also need a sales amount measure using a simple sum.
You see how these can be quite simple? It’s all about setting the model up well and then knowing how to put together some relatively simple combinations of measure. CALCULATE and FILTER and big ones to learn and understand well. I use these very often.
After using the formulas you mentioned, I was able to obtain the customers who have no purchases registered for the period I’m selecting, but what I really need is those accounts who NEVER EVER have registered any purchase since the account was activated, so the history sales for the account is ZERO.
I tried the formula but I got the following warning
This is truly driving me crazy, even though I know it should be very simple
What I just need is to be able to filter by Customer ID which ones from the Customer Data table are not in the sales table, regardless of the period selected, meaning that they NEVER EVER did a purchase …