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


#8

Hi Sam,

The context of the calculation will be the number of customers over a period of time, like in this example:

image

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

Obviously, I’m doing something wrong :frowning:

Lucia


#9

Great,

The first thing to realise whenever you see results like this it’s usually to do with the relationships in your model not working.

The measure isn’t being filtered correctly by the month and year so it’s doesn’t know what result to bring back.

So first I look to the model.

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.

Chrs


#10

Thank you, Sam, for your help.

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.

image

Can you please give me some guidance on this?

Thank you in advance …

Lucia


#11

What formula do you currently have? Let’s work on that. Add it here.

What’s likely is that within the CALCULATE function, you just need to use ALL( Dates)

This will probably be the solution.

Check out ALL here.


#12

Sam,

The formula I currently have is:

Account Zero Sales = 
    CALCULATE( [Active Customer],
    FILTER( 'Customer Data', [Total Net Sales] = 0))

I tried adding the ALL(Calendar) within the CALCULATE function but I’m getting the same result …

Account Zero Sales = 
    CALCULATE( [Active Customer],
        ALL('Calendar'),
             FILTER( 'Customer Data', [Total Net Sales] = 0))

For sure I’m not applying the function the correct way :frowning:

image


#13

Hello Sam,

I’m still trying to figure out how to resolve this :frowning:

Now the figures look better, but I’m again getting the customers with zero sales for the period selected. The formula I used was

Account Zero Sales =
CALCULATE( [Active Customer],
ALL( ‘Calendar’),
FILTER( Sales, Sales[NET SALES] =0 ))

image

I cannot find the correct DAX formula to obtain those accounts that have no sales registered EVER, even though the account was activated.

Any help will be greatly appreciated …

Lucia


#14

Good day @lbarbeyto,

Try moving the Filter function like this:

Account Zero Sales =
CALCULATE( [Active Customer],
FILTER(ALL( ‘Calendar’),
Sales, Sales[NET SALES] =0 ))

Jorge


#15

Thank you Jorge for your reply.

I tried the formula but I got the following warning

image

This is truly driving me crazy, even though I know it should be very simple :frowning:

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 …

image

Lucia


#16

Hi Lucia,

If you can share with me a dummy pbix file with the model I can try to get the results and get back to you with the pbix file.

Also, my bad I left an extra field in the formula:

Account Zero Sales =
CALCULATE( [Active Customer],
FILTER(ALL( ‘Calendar’),
Sales[NET SALES] =0 ))

Maybe that works, if not paste here the dummy pbix file and I will work with it to get the results done.

Jorge


#17

Hi Jorge,

I tried this last one but still is not working.

I’m sending you the dummy pbix file by email. Please, let me know if you don’t receive it.

Lucia


#18

Hi Lucia,

I haven´t received the pbix file, which email address did you sent it to?

Jorge


#19

Hi Jorge,

I sent it to info@enterprisedna.co

Lucia


#20

Ohh sorry Lucia, I don’t have access to that email account. Can you send it to j.galindo16@hotmail.com?

Thanks and sorry for the inconvenience.