Forum Discussion

kurumy's avatar
kurumy
Frequent Visitor
9 years ago

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

 

Three answer can  be possible :

  • New customer
  • A steady customer
  • A customer who stopped working with us.

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

 

Tutorial Requests

5 Replies

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    9 years ago

    kurumy,

     

    You may add measures as shown below.

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

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

    stopped =
COUNTROWS (
    DISTINCT (
        EXCEPT (
            SELECTCOLUMNS (
                FILTER ( ALL ( Table1 ), Table1[year] = MAX ( Table1[year] ) - 1 ),
                "customerId", Table1[customerId]
            ),
            SELECTCOLUMNS ( Table1, "customerId", Table1[customerId] )
        )
    )
)
    • kurumy's avatar
      kurumy
      Frequent Visitor
      9 years ago

      Hi :smileyhappy:

       

      Thanks that what i wanted !

       

      But i have a problem the measure "Stopped" is not working. It only show me "ALL" and don't integrate the filter.

      Do you have a solution for that ?

       

      Thanks