Forum Discussion

AH2022's avatar
AH2022
Helper II
4 years ago
Solved

Intersect Table returns list

Hi All, For this sample data :  there is a measure returning the list of clients who purchased 3 last consecutive years, (19-20, 20-21, 21-22) and NOT 22-23.   1Customers list 3yRow not TY...
amitchandak's avatar
amitchandak
Super User
4 years ago

AH2022 , the approach will not have customer name .

 

I am assume you have [Purchased in the Last three years] and [purchased this year]

 

then using the customer table 

 

Concatenatex(filter(Customer,not(isblank([Purchased in the Last three years])) && isblank([purchased this year] ) ) , Customer [Customer] &"-"& Customer [ Name] , " , " )

 

 

The same approach I followed here.

Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retention/ba-p/1377458

 

But use customer table, not values(Customer[Customer])

 

Approch used in this video -Customer Retention with Dynamic Segmentation, New/Lost/Retain Customer Count: https://www.youtube.com/watch?v=W4EF1f_k6iY

 

  • AH2022's avatar
    AH2022
    Helper II
    3 years ago

    I found also the solution with the Intersect code.

     

    Name of Customers list 1yAgo not TY =

     

    VAR _currentyear =

        YEAR ( TODAY () )

    VAR _oneyearago = _currentyear - 1

    VAR _currentyearnosalescustomers =

        SUMMARIZE (

            FILTER (

                CROSSJOIN ( VALUES ( Calendar[Year] ), VALUES ( Customer[Customer] ) ), VALUES ( Customer[Name] ) ),

                Calendar[Year] = _currentyear

                    && [Total Sales] = BLANK ()

            ),

            Customer[Customer], Customer[Name]

        )

    VAR _oneyearagocustomerslist =

        SUMMARIZE (

            FILTER (

                CROSSJOIN ( VALUES ( Calendar[Year] ), VALUES ( Customer[Customer] ) , VALUES ( Customer[Name] )),

                Calendar[Year] = _oneyearago

                    && [Total Sales] <> BLANK ()

            ),

            Customer[Customer],  Customer[Name]

        )

    RETURN

     

        CONCATENATEX (

                      

                    INTERSECT ( _currentyearnosalescustomers, _oneyearagocustomerslist ),

           

         UNICHAR(10) &   [Customer] &" , " & [Name] ,

           ", "

         

        )