Customer buying every year

Hi KimGriso 

This is a measure only approach:

Customer Sales with all years = 
VAR YearsWithDupes =
    // Build a table of all order dates and customers, with a computed "Year" column
    SUMMARIZE (
        ALL ( 'Table' ),                          // remove filters, work over entire table
        'Table'[Order Date],
        'Table'[Customer Name],
        "Year", YEAR ( 'Table'[Order Date] )
    )
VAR AllYears =
    // Count how many distinct years exist in the whole table
    COUNTROWS ( GROUPBY ( YearsWithDupes, [Year] ) )
RETURN
    SUMX (
        FILTER (
            // Build a per-customer table: one row per customer with sales and year count
            SUMMARIZECOLUMNS (
                'Table'[Customer Name],
                "@amount", SUM ( 'Table'[Sales Amt] ),   // total sales per customer
                "@CustomerYears",
                    COUNTROWS (
                        GROUPBY (
                            FILTER (
                                YearsWithDupes,
                                [Customer Name] IN VALUES ( 'Table'[Customer Name] ) // only that customer
                            ),
                            [Year]    // group by year
                        )
                    ) // count distinct years for that customer
            ),
            // Keep only customers whose distinct years match the overall distinct years
            [@CustomerYears] = AllYears
        ),
        [@amount]   // sum the sales of the qualifying customers
    )

There are other approaches in the attached pbix that requires a dedicated tables and helper columns in the fact table.

Performance difference amount these approaches is neglible with small tables but can be obvious otherwise especially for the approach above.