cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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:

 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.

1 ACCEPTED SOLUTION
Community Support

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
```

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.

2 REPLIES 2
Community Support

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
```

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.

Helper II

Thanks!

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.