Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Need help understanding Dax (Lost Customers)

Hi,  

 

I need a calculation that works out which customers have not spent with us this year.

 

I have tried to copy some code from the internet (Dangerous) but it does not work

 

Lost Customers = IF(
HASONEVALUE( insertBK[dateins] ) ,
COUNTROWS ( FILTER ( CALCULATETABLE ( VALUES( adDetails[TrueCompany]),
FILTER( ALL(insertBK[dateins]), insertBK[dateins] =
DATEADD( VALUES( insertBK[dateins] ), -1, MONTH )
), ALL(insertBK[dateins])
) ,
adDetails[TrueCompany]<>
CALCULATETABLE( VALUES ( adDetails[TrueCompany] ) )
)
))

To be honest I am njot sure what it is returning.  I have changed the tables in the code to where I think it should be pointing.

 

I am trying to understand what is going on and why it is not working, would someone be able to explain to me.

 

Thanks

 

Chris

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi Anonymous,

     

    If your sales table only contain the consumption records, you can filter the records by date(for example 2015) then get the customer column from the filtered table and use EXCEPT Function to filter data with customer table.

     

    If above is not help, can you share some sample data?

     

    Regards,

    Xiaoxin Sheng

4 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thanks guys, will lookk into that see how I get on.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Or something like

     

    Lost Customers = CALCULATE(sum(insertBK[InsertNetCost]),'Calendar$'[Year]=2015,var x = CALCULATE(sum(insertBK[InsertNetCost]),'Calendar$'[Year]=2015) = null)

     

    which doesnt work

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Hi Anonymous,

       

      If your sales table only contain the consumption records, you can filter the records by date(for example 2015) then get the customer column from the filtered table and use EXCEPT Function to filter data with customer table.

       

      If above is not help, can you share some sample data?

       

      Regards,

      Xiaoxin Sheng