Calculate metrics based on customers who purchased twice in the last year by month

[Dave_Gugg]

I need to create an anaylsis that looks at various metrics (Avg $ Total/Purchase, Gross Margin) by month for all customers who have made exactly two purchases in the past year.  So, for example, if a customer made a purchase in June 2016 and October 2016, I would expect their order metrics to be shown for each month between October 2016 and June 2017 (since for all months between these dates, the customer has made two purchases in the last year).  

I have a basic star schema with a Demand fact table that includes all the order metrics, an Order Date dimension table, and a Customer dimension table.

My attempt to build one of these metrics is to first identify the number of orders a customer has made in the past year.  This is a custom measure:

Customer Order Count in Previous Year = CALCULATE(DISTINCTCOUNT(Demand[OrderNumber]),FILTER(Demand,Demand[Order Date] <= Demand[Order Date] && Demand[Order Date] >= DATEADD(Demand[Order Date],-1,YEAR)))

I have validated that this metric works as expected by creating a table visual filtered down to a couple customers and a small range of dates.

From that I try to create a custom metric to get one of the aggregates I am looking to create, filtering my first metric to find two distinct purchases:

Average Purchase Amount = CALCULATE(AVERAGE(Demand[Ship $]),FILTER(Demand,[Customer Order Count in Previous Year] = 2))

This doesn't work.  I'm basically seeing no results when adding this measure to a visual.

Any ideas on what I need to adjust to create this analysis?

Thank you

[v-sihou-msft · Accepted solution]

@Dave_Gugg

I guess you create your measure in the fact table. So when you apply condition "FILTER(Demand,[Customer Order Count in Previous Year] = 2)", it will always return empty context because [Customer Order Count in Previous Year] can never be 2 on any detail row within fact table.

In this scenario, you should create it in the Customer Dimension table. Your [Customer Order Count in Previous Year] measure is to filter "Customers" mapping back to fact table for your Average calculation.

Regards,