cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
STS_Joshua Regular Visitor
Regular Visitor

Help Filtering

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:

CustomerPurchase Date
Bob2019
Bob2018
Jane2018
Mary2019
Mary2019
Joe2019
Joe2018
Joe2019
Joe2018
Mary2019
Mary2018
Jane2018

 

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:

 

CustomerPurchase Date
Jane2018
Jane2018

 

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Help Filtering

@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)

2.PNG 

 

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')

1.PNG

 

 

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.

2 REPLIES 2
Community Support Team
Community Support Team

Re: Help Filtering

@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)

2.PNG 

 

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')

1.PNG

 

 

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 Regular Visitor
Regular Visitor

Re: Help Filtering

Thanks!