Filter Customers based on 12 month sales totals

[CRamirez]

I can't seem to find anything online answering this kind of question which is odd because it seems like a relatively common issue that would occur.

I am trying to create a measure that will give me a count of customers that have over $500 in sales over the last 12 months. I have a measure that gives me customer counts and another that will give a customers 12 month sales total. The problem I am getting is that when I try to filter on customers with sales amounts over $500 I am only getting a count of customers with at least a single order over $500 for the past 12 months. This will not account for customers with multiple smaller orders that cumilate to over $500.

I am assuming the calculation I am looking for has something to do with SUMX, but it seems to be finicky when it comes to filters.

My current database is normalized and has a Customer dimension table with all relevant customer info as well as a Sales fact table. I have a date table as well which is what I use for my time-intelligence measures.

Any help would be greatly appreciated

[Ashish_Mathur]

Hi,

Share the link from where i can download your file.  Also, show the expected result.

[CRamirez]

@Ashish_Mathur

I'm sorry, but that's not possible. I don't believe downloading my file would be of much use in any case. In addition, I'm not sure what the expected result would be which is why I'm trying to determine this measure.

The relevant tables in my dataset is a simple Customer dimension and a Sales Fact table. Is there a way to filter out total customer sales below a certain amount over a 12 month period? For example, a count of customers whose total sales are over $500 over 12 months

[v-yulgu-msft · Accepted solution]

Hi @CRamirez,

Based on my assumption, table structure looks like below:

Please try measures: (In my test, I calculated total sales in last 3 month)

Total Sales last 12 month =
CALCULATE (
    SUM ( 'Sales Fact Table'[Sales] ),
    FILTER (
        ALLEXCEPT ( 'Sales Fact Table', 'Sales Fact Table'[Customer] ),
        'Sales Fact Table'[Date].[MonthNo] < MONTH ( TODAY () )
            && 'Sales Fact Table'[Date].[MonthNo]
                >= MONTH ( TODAY () ) - 3
    )
)


Count customer =
CALCULATE (
    DISTINCTCOUNT ( 'Sales Fact Table'[Customer] ),
    FILTER ( ALL ( 'Sales Fact Table' ), [Total Sales last 12 month] >= 500 )
)

Table visual.

Best regards,
Yuliana Gu