Find status for a customer

[Anonymous]

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?

[Ashish_Mathur]

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]

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]

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.

[v-eachen-msft]

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"
        )
    )
)

[amitchandak]

@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]

Hi @amitchandak  thanks for the quick reply,

sadly this still gives all three results for most customers.

[Pragati11]

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]

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/EXobgfCzdBVNs0EInc...

Let me know if you need anything else.

Kind regards,
Queenie

[amitchandak]

@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]

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