Forum Discussion

Varshi288's avatar
Varshi288
Resolver II
7 years ago

DAX Expression for comparing rows

Hi all,

 I need to find new customers and Lost Customers(region) based on some condition. Below is table and condition.

I need to categories region based on below condition.

If previous full year sales= 0 (sales L2y)  OR

Previous sales > 100% AND previous yes sales is <100 KUSD

Then current year YTD is : New customer else Lost Customers.  

 

    

PLease help  thanks inadvance.

 

Need Help

3 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi Varshi288,

     

    Based on your logic, we could use the IF function to create a calculated column or measure to achieve your requirement.

     

    current year YTD =
IF (
    'TableName'[previous full year sales] = 0
        || 'TableName'[Previous sales] > 0.1
        && 'TableName'[previous yes sales] < 100,
    "New customer",
    "Lost Customers"
)

    If you still need help, please share some data sample as table format and your desired output so that I can copy and test to get the solution more quickly.

     

    Best  Regards,

    Cherry

    • Varshi288's avatar
      Varshi288
      Resolver II
      7 years ago

      Hi Cherry,

      Thanks for your help and appolagies for late reply since I was working with other stuff. 

      The logic used here is getting all as new customers (old customers also). here is som rough data alog with required out put.

      Year201720182019
      CustomerKUSDNew CustKUSDVs PrY%New CustKUSDVs PrY%New Cust
      Abc1232989New Customer2922-2% 2453-16% 
      Abc1241268New Customer159426% 15940% 
      Abc1251291New Customer13333% 1110-17% 
      Abc1261083New Customer151039% 654-57%Lost Customer
      Abc127 New Customer974 New Customer792-19% 
      Abc128-72New Customer1211-1779%Lost Customer1169-3% 
      Abc129396New Customer47821% 81671% 
      Abc1301157New Customer410-65%Lost Customer79-81%Lost Customer
      Abc131     600 New Customer

       

      BR

      Varshi

      • v-piga-msft's avatar
        v-piga-msft
        Resident Rockstar
        7 years ago

        Hi Varshi288 ,

         

        Sorry I still have a little confused about your scenario.

         

        Could you share your sample data and the desired output in different table format so that it will be clear for us to identify which is the output you want?

        The logic used here is getting all as new customers (old customers also). 

         

        In addition, your logic is not very clear based your reply. Could you please explain your logic in more details?

         

        Best  Regards,

        Cherry