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.
It should really be a combination of CALCULATE and FILTER here, or use iterating functions like SUMX, COUNTX, AVERAGEX.
So youāll have your initial calculation and the you need to isolate what you do, and donāt want to count up by using the correct logic within filter.
I highly recommend gaining a better understanding of iterating functions. This logic is what allows you to solve this at a row based level (which seems like what you require)
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.
Just viewing the model concerns me a little with the overall setup you have here.
Have you been able to apply all the best practices from this course below into your model.
I can see a few relationships that donāt seem right and in theory your customer table should just be connected to your main fact table.
Also the lookup table just doesnāt seem to be required in my view.
This should all be completed with DAX measures.
For activation date Iām presuming all you require is MIN( Activation Date)ā¦thatās it really. This will return the first date a customer registers anything.
Highly recommend reviewing the above course in detail. This will put you on the right path very quickly and simplify what you are doing immensely.
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
COUNTROWS( FILTER(
RELATEDTABLE( 'Customer Data'),
'Customer Data'[Account Status] = "A"))
Zero Purchase =
CALCULATE (
[Active Customer],
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 ā¦
