Forum Discussion

kurumy's avatar
kurumy
Frequent Visitor
9 years ago

Lost customer between two years

Hello everyone Smiley Happy

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 interval to an another they  have stopped working with us. ( From 2016 to 2017).

 

I'm a beginner with DAX language but i would like to find a way to exprim my request. 

 

If you need more information about my request you can ask me futher details.

Best regards

Kurumy

 

Need Help
Tutorial Requests

9 Replies

  • 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

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