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

[GebauerAnalytic]

 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?

[v-juanli-msft]

Hi @GebauerAnalytic 

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

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.

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]

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