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 …
I have sent you the pbix file with a new Measures Table called “New Measures” there you can see the new measures I created for the filters.
I used a cummulative totals filtering the dates table, a logic function with 1 and 0 if the cummulative is BLANK() and a sumx to get the total of customers without sales.
I put 1 table for validation that has the customer ID from customer data and Customer ID from sales, that way I could see which are the clients ID that got no information on them. You will see the net sales is blank on them.
I left the original table with the original measures and the new measures table.
As you will see in the new table measures there is no amount from sales no matter the filters you apply from the date table.
