cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GebauerAnalytic Frequent Visitor
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])

Minus1=DATEADD(LASTDATE(Sales[ORDER DATE]),-1,MONTH)

Minus2=DATEADD(LASTDATE(Sales[ORDER DATE]),-2,MONTH)

Minus3=DATEADD(LASTDATE(Sales[ORDER DATE]),-3,MONTH)

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
Community Support Team

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

Hi @GebauerAnalytic 

I can get the result as below,

Capture1.JPG

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

 

For formatting, it supports to format values instead of "headers".

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.

Capture2.JPG

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.
GebauerAnalytic Frequent Visitor
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?

 

Memory Loss.PNG

Helpful resources

Announcements
Meet the 2020 Season 1 Power BI Super Users!

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?

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

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!

Power Platform Online Conference

Power Platform Online Conference

Join us for the first ever Power Platform Online Conference!

Top Solution Authors