Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Find status for a customer

Hi guys,   I am trying to build a report that can tell me whether a customer is new / current / lost. This depends on the when they last ordered something at the company. I thought I had it figur...
Need Help
Pragati11's avatar
Pragati11
Icon for Super User rankSuper User
6 years ago

Hi Anonymous ,

 

First thing I don't understand your DAX measure, looks like it has got error.

Can you tell me the check points correctly for different customer status, so that I can suggest a DAX for it?

Try sharing some sample data with example.

 

Thanks,

Pragati

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Pragati11 ,

 

it is actually part of a summarize table, that's why it might look a little funny.

 

But I made some sample data in excel which I hope might help. The idea is that any costumer that didn't order after 2017 is considered a lost costumer. If they ordered between 2017 and 2020 they are current and if they didn't order in 2018 or 2019, but they did in 2020 they are considered new.

 

https://iblsoftware1-my.sharepoint.com/:x:/g/personal/queenie_barends_truelime_nl/EXobgfCzdBVNs0EIncGtID0Bw0DQaWNt4x1V5XIwlTt2sw?e=4C8nD8

 

Let me know if you need anything else.

 

Kind regards,
Queenie

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Assume you have 4 different column , then you need to have something like this

     

    Switch(true(),
not(isblank([Sales_2020])) && (isblank([Sales_2019])) && (isblank([Sales_2018])), "New"	,	
not(isblank([Sales_2020])) && (not(isblank([Sales_2019])) || not(isblank([Sales_2018]))), "Current"	,	
(isblank([Sales_2020])) && (isblank([Sales_2019])) && (isblank([Sales_2018])), "Lost"
)
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak ,

       

      It is 4 different columns, but it is grouped by reservation date. This means that I have multiple records for one year and that is probably why my first idea doesn't work.

       

      Kind regards