Sum of Sales for Customers who bought in 2 specific years

[ed_mcdill]

Hi

I have a Sales fact table, with Customer and Dates dim tables.

I would like to return the sum of sales for customers, Sales[Total Sales], for customers Customer[Name], who made a purchase in both Dates[CY]="CY17" && Dates[CY]="CY18"

I seem unable to create a filter for two CY's, any pointers?

[fhill]

There is probably a more programtic way of doing this, but this is one solution:

1. Filter you Date Column for 1/1/2016 and After.  Duplicate your Date Column in Query Editor and Split the new column to only extract the Year (choose your delimter and Left/Right most depending on your date format)  You now have a new column of Year. 

2.  Duplicate your Table (Query) and delete everthing but CustomerID and Year from the new Table (Query).  Select both Columns and 'Remove Duplciate Rows'.  Now you only have a list of customers and each year they placed an order.

3. Use the' Group By' feature on the 'Transform' tab.  Group by CustomerID and "Count Distint Rows" under Operation.  You now have 1 line per customer with how many DISTINCT Years they placed orders.

4. Close and Apply Query Editor and Manage Relationships to link your Customers from the new Table (Query) to the original Table (Query).

5. Create your visual and apply a filter for the Discinct Years Count Column you created in Step 3.  Set you filter to 2.

Here's a sample table I created and the end result following these steps:

Sample below.

[ed_mcdill]

Thanks for the reply. I must admit I've been trying to find a solution through a measure as I access this through a SSAS TM so don't have the ability to make changes to the data sources.

[v-huizhn-msft]

Hi @ed_mcdill,

You can a calculated column in Fact table using the formula: CY=RELATED(Dates[CY])

Then create a new table to get the sale records by clicking "New Table" under Modeling on Home page, type the formula.

FilteredTable=INTERSECT(FILTER(FactTable, FactTable[CY]="CY18"),FILTER(FactTable, FactTable[CY]="CY18"))

Then calculated what you want using the similar formula.

sum=CALCULATE(SUM(FilteredTable[Sales]),ALL(FilteredTable))

Best Regards,
Angelia

[ed_mcdill]

Many thanks, maybe I'll contact our db team to see if they will put this in for me.

[v-huizhn-msft]

Hi @ed_mcdill,

Got it, please mark the helpful reply as answer, or share your own solution, so that more people will benefit from here.

Best Regards,
Angelia

[ed_mcdill]

Thanks, I'm still trying to solve this using a calculated measure and will be sure to post here once I do.

[v-huizhn-msft]

Hi @ed_mcdill,

Got it.

Best Regards,
Angelia