Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
STS_Joshua
Helper II
Helper II

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
v-yuta-msft
Community Support
Community Support

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

View solution in original post

2 REPLIES 2
v-yuta-msft
Community Support
Community Support

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

Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.