Unique Customer Count - Rolling 12 Months

[ryanraff37]

Hello,

I have a bit of an odd one.  I have a sales table with customer information. I am counting the # of unique customers over 12 months to track how the client base changes over time.  Within my sales table, there are different sales channels a customer can transact on.  Each sales channel has different critiera, in addition to the 12 month time period, in order to qualify that customer as a valid unique customer. 

To make it a bit more complicated, customers can spend across channels as well.  So I am tracking unique customers across 3 different channels and unique customers in aggregate.  I have a jnaky solution, but the unique aggregate customers that checks against 3 different criteria checks is running out of memory when I try to use the measure for any type of visual other than a card.

Here's one of the measures that looks at a specific sales channel:

CALCULATE(
    countrows(
    FILTER(
        SUMMARIZE(
            CALCULATETABLE(
                VALUES('Sales Table'[Customer ID]),
                DATESBETWEEN(
                    'Date'[Date],
                    EDATE(MIN('Date'[Date]),-11),
                    MAX('Date'[Date])
                )
            ),
            'Sales Table'[Customer ID],
            "Condition",
                CALCULATE(
                    SUM('Sales Table'[Sales Amount]),
                    DATESBETWEEN(
                    'Date'[Date],
                    EDATE(MIN('Date'[Date]),-11),
                    MAX('Date'[Date])
                    )
                )

        ),
        [Condition] >1
    )
    ),
    'Sales Table'[Salse Channel] ="Sales Channel A",
    'Sales Table'[Product Type] = "Product Type A"
    
)

These measures seem to work fine, and work across various visuals.  Here is where my real challenge is...finding an efficient measure that accomplishes the same for my client base in aggregate with the unique criteria applie to the appropriate sales channel.
Note: [Sales Amount] is just the sum of 'Sales Table'[Sales Amount].

Test Measure = 
var table_grouped =
    SUMMARIZE(
        'Sales Table',
        'Sales Table'[Customer ID],
        "R12 Sales Channel A", 
         CALCULATE(
            [Sales Amount],
                 DATESBETWEEN(
                'Date'[Date], 
                EDATE(MIN('Date'[Date]),-11),
                MAX('Date'[Date])),
                'Sales Table'[Sales Channel] = "Sales Channel A",
                'Sales Table'[Product Type] = "Product A"),
        "R12 Sales Channel B",
        CALCULATE(
            [Sales Amount],
                DATESBETWEEN(
                'Date'[Date], 
                EDATE(MIN('Date'[Date]),-11),
                MAX('Date'[Date])),
                'Sales Table'[Sales Channel] = "Sales Channel B"),
        "R12 Sales Channel C",
        CALCULATE(
            [Sales Amount],
                DATESBETWEEN(
                'Date'[Date], 
                EDATE(MIN('Date'[Date]),-11),
                MAX('Date'[Date])),
                'Sales Table'[Sales Channel] = "Salse Channel C"))

Return 
    COUNTROWS(
        FILTER(
            table_grouped,
            [R12 Sales Channel A]>10000 ||
            [R12 Sales Channel B] >1 ||
            [R12 Sales Channel C] > 1))

I mean, it works. But it seems so terribly inefficient and I haven't been able to crack how to clean it up. 

Any help or advice would be extremely appreciated.

[v-zhangti]

Hi, @ryanraff37 

Please try this optimised formula.

Test Measure =
VAR sales_table_filtered =
    FILTER ('Sales Table',
        'Sales Table'[Sales Channel]
            IN { "Sales Channel A", "Sales Channel B", "Sales Channel C" }
            && 'Sales Table'[Product Type] = "Product A" )
VAR date_range =
    DATESBETWEEN ('Date'[Date],
        EDATE ( MIN ( 'Date'[Date] ), -11 ),
        MAX ( 'Date'[Date] ) )
VAR table_grouped =
    GROUPBY ( sales_table_filtered,'Sales Table'[Customer ID],
        "R12 Sales Channel A",
            CALCULATE ([Sales Amount],date_range,
                'Sales Table'[Sales Channel] = "Sales Channel A"
            ),
        "R12 Sales Channel B",
            CALCULATE ([Sales Amount]date_range,
                'Sales Table'[Sales Channel] = "Sales Channel B"
            ),
        "R12 Sales Channel C",
            CALCULATE ([Sales Amount],date_range,
                'Sales Table'[Sales Channel] = "Sales Channel C"
            )
    )
RETURN
    COUNTROWS ( FILTER ( table_grouped,
            [R12 Sales Channel A] > 10000
                || [R12 Sales Channel B] > 1
                || [R12 Sales Channel C] > 1
        )
    )

Hope this helps.

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ryanraff37]

Hello - apolgies for missing this reply, I guess my email notifcaitons are not enabled.

I attempted the above solution was given this error:
Function 'GROUPBY' scalar expressions have to be aggregation functions over CurrentGroup().  The expression of each aggregation has to be either a costant or directly reference the columns in CurrentGroup().

I tried to fix for this by creating this formula:

GROUPBY ( sales_table_filtered,'Sales Table'[Customer ID],
        "R12 Sales Channel A",
            CALCULATE (
             SUMX(CURRENTGROUP(),
            'Sales Table'[Sales Amount]),
             date_range,
            'Sales Table'[Sales Channel] = "Sales Channel A"
         ),continued

I tried a few other iterations of summing revenue (e.g. adding CURRENTGROUP() as one of the calculate filter conditions), to avoid the error as well as playing with the first variable to see if that might have been causing issues.  

Not the end of the world as I hope the internal BI team I work with can solve this in the long run.  Just a frustration that I can't solve it haha.  Very much appreciate the effort in trying to help.