Customers with no sales ny month

[BORUCH]

Hi All

I have two tables one is my all customer table, which has my customer number ,customer name, with no sales in it.

My other table is my sales table which also has customer number, customer name, invoice number, invoice date, and amount.

There is only one relationship customer # in my customer table, and customer number in my sales table.

What I would like to accomplish is to be able to see by Month what customers had sales and what customers did not have sales I don’t want to use my customer number from my sales table, because my sales table only has customer # that have sales in it

if I grab my customer number from my customer table and the total invoice amount from my sales table I will not see which customers had no sales

I have a table called calendar but the problem is that my customer table has no column that has anything to do with dates

Any help is appreciated

 

[Ozeroth]

No problem, glad it worked!

One way of ensuring you only count customers with Invoice Amount >0 is to replace each occurrence of Sales with CALCULATETABLE( Sales, Sales[Invoice Amount] > 0 )

I've updated the measures in the same dropbox link:
https://www.dropbox.com/s/6bsjeezuv3o7qxs/Customers without Sales.xlsx?dl=0

There could be other ways of doing it.

 

[BORUCH]

No problem, glad it worked!

One way of ensuring you only count customers with Invoice Amount >0 is to replace each occurrence of Sales with CALCULATETABLE( Sales, Sales[Invoice Amount] > 0 )

I've updated the measures in the same dropbox link:
https://www.dropbox.com/s/6bsjeezuv3o7qxs/Customers without Sales.xlsx?dl=0

There could be other ways of doing it.

hi Ozeroth

This worked perfectly thanks very much

I was just wondering if we can take this a step further I would like to be able to have a calculated column on my main customer table and it should tell me how often this customer buys is it a once a year customer, a once a month customer a twice a month customer a three times a month etc.

It should take the information from my sales table that has invoice dates on them

 

[Ozeroth]

Hi again,

As a starting point, you could define something like this, either as a measure, or as a calculated column in the Customer table.

This computes Sales per Day over the date range from earliest invoice to latest invoice.
You could modify this to express as Sales per Month etc.

You may want to define the date range differently as well.

As a calculated column in the Customer table, it calculates in the context of the current row's Customer.
As a measure, it calculates the same thing in the current context (date, Customer, etc)

=
CALCULATE(
    DIVIDE (
        COUNTROWS ( Sales ),
        MAX ( Sales[Invoice Date] ) - MIN ( Sales[Invoice Date] ) + 1
    ),
    Sales[Invoice Amount] > 0
)

Are you wanting to use this for some sort of segmentation of customers?

 

[BORUCH]

Hi again,

As a starting point, you could define something like this, either as a measure, or as a calculated column in the Customer table.

This computes Sales per Day over the date range from earliest invoice to latest invoice.
You could modify this to express as Sales per Month etc.

You may want to define the date range differently as well.

As a calculated column in the Customer table, it calculates in the context of the current row's Customer.
As a measure, it calculates the same thing in the current context (date, Customer, etc)

=
CALCULATE(
    DIVIDE (
        COUNTROWS ( Sales ),
        MAX ( Sales[Invoice Date] ) - MIN ( Sales[Invoice Date] ) + 1
    ),
    Sales[Invoice Amount] > 0
)

Are you wanting to use this for some sort of segmentation of customers?

Hi good morning

yes i would want some type of segmentation of customers. I would probably have to create a new table with all different types of categories once a month, once a year, twice a month, twice every two months etc. and somehow link it to the customer and sales table

 

[Ozeroth]

Sounds like you may want to do static segmentation similar to this:
Static Segmentation – DAX Patterns

You could do this using the above calc column or something similar.