Forum Discussion

batmit25's avatar
batmit25
Frequent Visitor
2 years ago
Solved

VALUES() returning blank - Customer Churn

Hi everyone,   I'm trying to work on a Customer Churn Report and as a beginning step I need to classify customers as New,Retained and Lost for every month selected via a slicer. The logic here is t...
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi batmit25 ,

You can use the following DAX to create a new column:

Lost Customers = 
IF(NOT ISBLANK([Sales Last Month]) && ISBLANK([Sales This Month]), 1, BLANK())

New Customers = 
IF(ISBLANK([Sales Last Month]) && NOT ISBLANK([Sales This Month]), 1, BLANK())

Retained Customers = 
IF(NOT ISBLANK([Sales Last Month]) && NOT ISBLANK([Sales This Month]), 1, BLANK())

And the final output is shown in the following figure:

Best Regards,

Dino Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

 

batmit25's avatar
batmit25
Frequent Visitor
2 years ago

Hi Anonymous ,

 

The IF conditions are right I believe!

Could I also know how you were able aggregate the Measures in order to show Total Customers(Lost,New,Retained). I used SUMX alongside but the values returned were blank.

 

Thank you again :))

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi batmit25 ,
    The total of the previously provided solution is not calculated by DAX, but by the built-in calculation function of Power BI Desktop:

     

    And if you want to calculate the total by DAX, you can try the following DAX to create a new measure:

    Total Lost Customers = COUNTROWS(FILTER('Table','Table'[Lost Customers] = 1))

    Or you can use this DAX as following:

    Total New Customers = 
SUMX(FILTER('Table','Table'[New Customers] = 1),'Table'[New Customers])

    And the final output is shown in the following figure:

    Best Regards,

    Dino Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

    • batmit25's avatar
      batmit25
      Frequent Visitor
      2 years ago

      Hi Anonymous,

      The DAX measures work perfectly for Retained and New Customers, However while trying to aggregate the Lost Customers value. I run into the same problem of everything being blank.

      I'm not sure whether it's related to how I modeled my data but if need be I can share my report(Just a trial report). Let me know if there's anything I can add to solve this issue. 

       

      Thanks again much appreciated!

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi, batmit25 
        Yes, you can share your report with me, but please note that before you share it, please check if it contains any private information (such as account number and password, etc.), and if so, please delete it before sharing.