Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Unique Customer Count - Rolling 12 Months

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 m...
Need Help
Tips and Tricks
v-zhangti's avatar
v-zhangti
Icon for Community Support rankCommunity Support
3 years ago

Hi, Anonymous 

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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.