Forum Discussion

kurumy's avatar
kurumy
Frequent Visitor
9 years ago

Lost customer between two years

Hello everyone i've been asked to find a way to compare customers activity between two years. I don't need to have the exact values of their purchases but i have to show if between one time inte...
Need Help
Tutorial Requests
fhill's avatar
fhill
Resident Rockstar
9 years ago

How is your customer data formatted?  Is is a list of Invoices with Dates and Customer ID's, or do you have something more Matrix based with annual sales?

 

FOrrest

kurumy's avatar
kurumy
Frequent Visitor
9 years ago

In fact it is a table of the annual sales.

Each round represent a command and it is compose by differents columns which provide information like the customer name, dates, the product...

 The thing is than i have already found a way to show the new customers from one year to another but i can't manage to show customers whose have been lost.

 

This is the formula i use of new customers.

new =
COUNTROWS (
    DISTINCT (
        EXCEPT (
            SELECTCOLUMNS ( Table1, "customerId", Table1[customerId] ),
            SELECTCOLUMNS (
                FILTER ( ALL ( Table1 ), Table1[year] = MAX ( Table1[year] ) - 1 ),
                "customerId", Table1[customerId]
            )
        )
    )
)

 

    • kurumy's avatar
      kurumy
      Frequent Visitor
      9 years ago

      Hi v-huizhn-msft

       

      Thanks for your answers.

       

      Find below a sample of my table.

       

      Thanks for your help :smileyhappy:

      Best regards,

      Kurumy

      • kurumy's avatar
        kurumy
        Frequent Visitor
        9 years ago

        Dear v-huizhn-msft

         

        Do you have a solution ?

         

        Best regards.

        Kurumy