cancel
Showing results for
Did you mean:
Frequent Visitor

## Filter Customer By Whether Its Top Product Has Changed in the Past 3 Months

I am trying to create a filter for a table or matrix such that it only shows customers who have had a change in their top product by sales in the past 3 months.

This is a simplified source table:

 Customer Order Date Product Sales A 11/1/19 1000A 12 A 11/1/19 1001E 8 A 10/1/19 1000A 7 A 10/1/19 1001E 10 B 11/1/19 1001E 10 B 10/1/19 1001E 8

And the result I'd like would be something like this:

 Customer 3 Months Ago 2 Months Ago Last Month This Month A 1001E 1001E 1001E 1000A

Customer B is not showing because its top product did not change during the time period. It conditionally formats the month in which the change occurred for easier consumption.  If there's a change within 3 months, it passes the filter and highlights the month it changed. Towards this end, I've done some DAX to handle these procedures:

``````This_Month=LASTDATE(Sales[ORDER DATE])

Top_Product_This_Month = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Sales[PRODUCT]), RANKX(ALL(Sales[PRODUCT]), SUM(Sales[SALES]),,ASC)),1),FILTER(Sales, Sales[ORDER DATE]=[This_Month]))

Top_Product_Minus1 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Sales[PRODUCT]), RANKX(ALL(Sales[PRODUCT]), SUM(Sales[SALES]),,ASC)),1),FILTER(Sales, Sales[ORDER DATE]=[Minus1]))

Top_Product_Minus2 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Sales[PRODUCT]), RANKX(ALL(Sales[PRODUCT]), SUM(Sales[SALES]),,ASC)),1),FILTER(Sales, Sales[ORDER DATE]=[Minus2]))

Top_Product_Minus3 = CALCULATE(FIRSTNONBLANK(TOPN(1,VALUES(Sales[PRODUCT]), RANKX(ALL(Sales[PRODUCT]), SUM(Sales[SALES]),,ASC)),1),FILTER(Sales, Sales[ORDER DATE]=[Minus3]))``````

But beyond this point, I have been having constant problems. I've tried scrapping the individual measures and putting them as variables in one big honking measure, but I keep getting errors. Besides, it’s way too clonky to do any additional filtering or manipulation beyond this point. Does anyone have any ideas for a way forward, or a way to do this more efficiently?

2 REPLIES 2
Community Support Team

## Re: Filter Customer By Whether Its Top Product Has Changed in the Past 3 Months

I can get the result as below,

I create a calculated column,

``month/year = FORMAT([Order Date],"mm-yyyy")``

then create measures

``````m-sales = SUM('Table'[Sales])

rank =
RANKX (
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Customer] = MAX ( 'Table'[Customer] )
&& 'Table'[month/year] = MAX ( 'Table'[month/year] )
),
[m-sales],
,
DESC
)

count =
CALCULATE (
COUNT ( 'Table'[Product] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Customer] ),
'Table'[Product] = MAX ( 'Table'[Product] )
&& [rank] = 1
&& 'Table'[Order Date] <= MAX ( 'Table'[Order Date] )
&& DATEDIFF ( 'Table'[Order Date], TODAY (), MONTH ) <= 4
)
)

top1 =
CALCULATE (
MAX ( 'Table'[Product] ),
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Customer] ),
'Table'[month/year] = MAX ( 'Table'[month/year] )
&& 'Table'[rank] = 1
)
)

changetimes =
COUNTX (
FILTER (
ALLEXCEPT ( 'Table', 'Table'[Customer] ),
DATEDIFF ( 'Table'[Order Date], TODAY (), MONTH ) <= 4
&& [count] = 1
),
[count]
)

final =
IF (
DATEDIFF ( MAX ( 'Table'[Order Date] ), TODAY (), MONTH ) <= 4
&& [count] <> BLANK ()
&& [changetimes] >= 2,
[top1]
)
``````

https://docs.microsoft.com/en-us/power-bi/desktop-conditional-table-formatting

To format the header, you can change the format of the column header with a "bold" size.

Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Frequent Visitor

## Re: Filter Customer By Whether Its Top Product Has Changed in the Past 3 Months

@v-juanli-msft  While it appears to work when there are only two customers and two products (looking at your pbix), it absolutely chokes on 8000 customers/10 products and does the little whirly circle for fifteen minutes until it gives a memory error, even fresh off rebooting. Is there any way to make this more efficient so as to work in-situ via a shared report?

Announcements

#### Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

#### Super User Challenge: Can You Solve These?

We're celebrating the start of the New Super User season with our first ever Super User 'Can You Solve These?' challenge.

#### Power BI Desktop Update - February 2020

We are super excited for our update this month, as we are releasing two of our top community requests!