Accounts activated but with no sales registered


#1

Hello Sam,

I need your help again resolving the following:

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.

image

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.

Thank you in advance for your help!

Lucia


#2

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)

See below for more info.


#3

Thank you Sam for your reply,

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.

This is the formula used:

Activation Date = DATEADD( 'CUSTOMER MASTER'[Activation Date],0,DAY)

Any help will be very appreciated.

Lucia


#4

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.

Thanks


#5

Thank you Sam, I’ll work on the data model and get back to you if I still have questions :slight_smile:

Lucia


#6

Hi Sam,

After reviewing your videos, I have been able to organize the data model. Here you can see the difference.

From this …

To this …

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 :frowning:

Regards,

Lucia


#7

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.

Thanks