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

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.

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