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 figured out with a summarize table, but the column will sometimes give all three different results for one single customer. It needs to be a column because we need to be able to sort on the customer status.

 

Below you can find what I use now in my summarize table:

"Status"; IF(CALCULATE(SUM('Reservations'[Amount]); 'PowerBI_Calendar'[Year] > (YEAR(TODAY())-3); 'PowerBI_Calendar'[Year] < YEAR(TODAY())) > 0 ; "Current" ; IF(CALCULATE(SUM('Reservations'[Amount]); 'bo PowerBI_Calendar'[Year] = YEAR(TODAY())); "New"; "Lost"))
 
Can anybody help?
 
Need Help

10 Replies

  • 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"
)
  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Anonymous , Try based on date like

    Summarize(Customer,Customer[Csutomer],"Status",
				Switch( True(),
					datediff(max(Reservations[Reservations Date]),today(),Year) >= 3 ,"Lost",
					datediff(max(Reservations[Reservations Date]),today(),Year) >= 0 ,"Current",
					"New"))
    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi amitchandak  thanks for the quick reply,

       

      sadly this still gives all three results for most customers.

  • v-eachen-msft's avatar
    v-eachen-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

     

    You could unpivot these 4 columns as one column in the query editor. Then you could remove "Sales_" and only keep years.

    Then you could use SUMMARIZE().

    Table =
SUMMARIZE (
    Sheet1,
    Sheet1[Accountnumber],
    "Status", IF (
        CALCULATE (
            SUM ( Sheet1[Value] ),
            FILTER (
                Sheet1,
                Sheet1[Attribute]
                    > ( YEAR ( TODAY () ) - 3 )
                    && Sheet1[Attribute] < YEAR ( TODAY () )
            )
        ) > 0,
        "Current",
        IF (
            CALCULATE (
                SUM ( Sheet1[Value] ),
                FILTER ( Sheet1, Sheet1[Attribute] = YEAR ( TODAY () ) )
            ) > 0,
            "New",
            "Lost"
        )
    )
)

     

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

    Hi,

    What is the purpose of the Status column there?  Isn't this the result you are expecting?  Also, would you be OK if Status is a measure (not a calcuated column formula)?

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Hi Ashish_Mathur ,

       

      I would be totally fine with a measure. However, I can't use a measure to filter the results.

      That's why I am using a calculated column.

       

      The result I would want is that there is one status for each accountnumber. Not (a different) one for each individual record

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

        Hi,

        You may download my solution PBI file from here.  I have used only measures to solve the problem which is ahy i can select Current, New and Lost in a slicer.  No value will appear for Lost because we do not have data prior to 2017.  Once you add sales data prior to 2017 to your dataset, you will see the customers lost figure aas well.

        Hope this helps.