Sales after customer has been called

[Anonymous]

i have a table with a customer number and the date of a follow up call.  I want to calculate the total sales for the customers from that list that occured after the follow up call.  Note, many of these customers had sales prior to the follow up call.  I don't care about those sales.  

1.) I have a customer Dimension table with Customer number and demograpics linked ot the Sales fact with the customer key

2.) I have a date Dimension Table linked to the sales fact with the sales date key

3.) I have a Sales Fact Table with customer key, sales date key, and sales amount

4.) I have my mentioned customer table with the customer number and follow up call date.

I assume i like the customer number to the customer Dim and the follow up call date to the date dim.  

So basically i want total sales of the list of customers after the follow up call date.

The sum of sales is easy.  filtering for only the follow up date is harder.

[Anonymous]

[Sales after FU Call] =
// get the currently visible
// customers
var CustsWithFUCalls = 
    SUMMARIZE(
        Customer[Customer Number],
        Customer[FU Call Date]
    )
var Result =
    SUMX(
        CustsWithFUCalls,
        // for each customer with FU call date
        // calculate the sales taking into
        // account the FU call date and the
        // current date context
        var FUCallDate = Customer[FU Call Date]
        return
            CALCULATE(
                [Total Sales],
                KEEPFILTERS(
                    'Date'[Date] >= FUCallDate
                )
            )
    )
return
    Result

[PaulOlding]

You could use this measure without having to link follow up call date to the date dim.

The columns in the dimension tables are available when iterating the fact using the RELATED function

Sales after follow up =

SUMX(

FILTER(Sales,

RELATED('Date'[Date]) > RELATED(Customer[Follow up call date])

),

Sales[Amount]

)

[Anonymous]

// A different formulation.
// Could be faster than others...

[Sales after FU Call] =
var CustsWithIndividualDates =  
    SELECTCOLUMNS(
        GENERATE(
            SUMMARIZE(
                Customer[Customer Number],
                Customer[FU Call Date]
            ),
            var FUCallDate = Customer[FU Call Date]
            return
                CALCULATETABLE(
                    DISTINCT( 'Date'[Date] ),
                    KEEPFILTERS(
                        'Date'[Date] >= FUCallDate
                    )
                )
        ),
        "@CustNumber", Customer[Customer Number],
        "@Date", 'Date'[Date]
    )
var Result =
    CALCULATE(
        [Total Sales],
        CustsWithIndividualDates,
        REMOVEFILTERS( 'Date' )
    )
return
    Result

Of course, [Total Sales] is just a plain sum over the correct column in the fact table.