Help Filtering

[STS_Joshua]

Hi all,

I'm having trouble filtering a table.

I have a table that lists customers and their purchase dates. The goal is to show how many customers bought in 2018, how many bought in 2019, and then who bought in 2018 but hasn't bought in 2019.

Sample table time:

Customer	Purchase Date
Bob	2019
Bob	2018
Jane	2018
Mary	2019
Mary	2019
Joe	2019
Joe	2018
Joe	2019
Joe	2018
Mary	2019
Mary	2018
Jane	2018

In this case I can filter a table of only 2018 purchases and another filtered table of only 2019 purchases by doing something like:

2018 = FILTER('Table1', YEAR('Table1'[Purchasedate])=2018) 

I don't know how to get a table that will end up looking like this:

Customer	Purchase Date
Jane	2018
Jane	2018

This is sample data, the actual originial data has 320k orders across 2 years with 721 distinct customers so it's a bit unweildly to do by hand.

Thanks in advance!

[v-yuta-msft · Accepted solution]

@STS_Joshua ,

You can create two measures to achieve amount in 2018 and 2019 seprately:

Customers Amount 2018 = CALCULATE(DISTINCTCOUNT('Table'[Customer]), FILTER('Table', 'Table'[Purchase Date] = 2018))

Customers Amount 2019 = CALCULATE(DISTINCTCOUNT('Table'[Customer]), FILTER('Table', 'Table'[Purchase Date] = 2019))

Then you can create a temp table via click "New Table" using DAX below:

Customers Amount in 2018 not in 2019 = 
VAR Table_2018 = SUMMARIZE(FILTER(ALL('Table'), 'Table'[Purchase Date] = 2018), 'Table'[Customer])
VAR Table_2019 = SUMMARIZE(FILTER(ALL('Table'), 'Table'[Purchase Date] = 2019), 'Table'[Customer])
RETURN
ADDCOLUMNS(EXCEPT(Table_2018, Table_2019), "Date", 2018)

 

Finanly, create a measure to count customers who purchased in 2018 but not purchased in 2019 in the new temp table:

Customers Amount in 2018 not in 2019 = COUNTROWS('Customers Amount in 2018 not in 2019')

You may also refer to pbix attached.

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[STS_Joshua]

Thanks!