Client Status Report: "Repating", "New" or "Lost" clients relative to another period

[PaulDBrown]

Good Afternoon from "sunny" Spain...!

I was hoping someone could point me in the appropriate directio to understand how to generate a report, based on a sales table including (Client Name, Date of purchase, ...) which will allow for the addition of billings according to their status ("Repeating", "Lost" or "new client") when comparing their purchases between to periods.

The idea of what I'm trying to achieve is as follows (Excel Example):

(I guess I haven't completely succeeded in getting my brain out of Excel mode...)

It's not the structure of the report I'm actually after, but rather how do I go about classifying the clients base on whether they are repeating, new or lost customers when comparing two periods.

Is there an example or tutorial available to anyone's knowledge?

Thank you so much for your help.

Best regards,

Paul.

[nairisha]

Hi,

I see that you have been working with excel, but if you would like to create report on Power BI, you will need to first normalize the data.

Here I have broken down the first table into two separate tables.

 Table 1 - Client

and Table 2 - Billing

I've used this table format to reduce data redundancy. Now you can use this data structure to create the reports you want.

Here is just a visual I quickly created. You can do lot more that what I have created.

Hope this will help!

Regards,

Nairisha 

[PaulDBrown]

Nairisha;

thank you so much for putting that example together! However (and this is entirely my fault for not being clearer in my original question..) the data set does not reside in Excel; the example I included was to ilustrate what I would be calculating IF I were working in Excel.

My data table  is in Power BI and it's structure actually reads like this:

And what I need is to be able to establish each client's status (as Repeat, Lost, New) within Power BI to be able to do the calculations described in the excel example included inmy original post.

Thank you so much for putting the example together.

All the best,

Paul.

[nairisha]

Hi @PaulDBrown,

How are you determing the status here?

Regards,

Nairisha

[PaulDBrown]

@nairisha

Nairisha,

"How are you detecting Status here?"

(the "data table in Power BI"  is actually the original excel table "unpivoted" so to speak...)

That is my point. What I need to do is to be able to establish which clients in my data table are "repeating, new or lost clients" relative to the previous year (or same month previous year if a month column is included).

The model I'm building is for media sales houses and this is a pretty standard analysis providing insights into portfolio performance. (Another analysis involves calibrating how the sales price has evolved over two periods depending on changes in selling price negotiation and/or sales volume per advertiser and how that impacts the aggregate final selling price for that period - this analyisis also needs to be able to compare performance of individual advertisers over two periods.) These types of analyses are pretty straightforward in Excel of course but I'm completely lost as to how they can be done in Power BI.

So I'm stuck basically.

Thank you very much for your help!

Regards,

Paul.

[dedelman_clng]

After more playing around, and seeing your actual data structure in PBI, here is the code for a calculated column to get what you want.  PLEASE NOTE this only works if 2016 and 2017 are the values for YEAR.  I would ask someone else to expand on the code to make this year / last year values based on the actual data values in the row

CustomerType = 
var TYS = calculate(values(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]), Tab[Year]=2017))
var LYS = calculate(values(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]), Tab[Year]=2016))
return
IF (AND( TYS = 0, LYS > 0), "LOST",
    IF(AND(TYS <> 0, LYS <> 0), "REPEAT",
       IF(AND(TYS <> 0, LYS = 0), "NEW", BLANK()
       )
    )
)

[PaulDBrown]

@dedelman_clng

David, Sorry to bother you again on this issue. Is there anything I have to do to the syntax in the formula (apart from changing the table/column names)?

I can't get past

var TYS

in the first line (I get an error saying and EoF is expected..)

Apologies again since I'm very new to all of this..

Thanks again,

Paul.

[nairisha]

@dedelman_clng and @PaulDBrown

Expanding on @dedelman_clng's DAX here is an updated one with  current year and last year.

 CustomerType = 
var TYS = calculate(values(Table4[Billing]), filter(ALLEXCEPT(Table4, Table4[Client Name]), Table4["Date"]=Year(TODAY())))
var LYS = calculate(values(Table4[Billing]), filter(ALLEXCEPT(Table4, Table4[Client Name]),  Table4["Date"]=YEAR(TODAY())-1))
return
IF (AND( TYS = 0, LYS > 0), "LOST",
    IF(AND(TYS <> 0, LYS <> 0), "REPEAT",
       IF(AND(TYS <> 0, LYS = 0), "NEW", BLANK()
       )
    )
)

[PaulDBrown]

@nairisha

Actually looking at the detail in the final table having applied to calculated column, isn't there something wrong? D"dates with values of "2015"  get a result. Shouldn't these rows remain blank? (ie. 2015 does not compute within either TYS or LYS variables...)

[PaulDBrown]

Thanks nairisha!!

Any chance you can lend me hand with the syntax (apart from changing the table and column names...): I can't get past the

var TYS when I paste the formula into the new column table formula box...

Thanks again!!

Paul.

[nairisha]

Hi @PaulDBrown,

Can you send me a screenshot. This is how the formula looks  at my end.

Nairisha

[PaulDBrown]

@nairisha

Here is the screenshot:

Thank you for your help!

Regards,

Paul.

[dedelman_clng]

@PaulDBrown you may need to replace the commas with semi-colons (I noted from your original post that you are in Europe).  There is probably some localized setting (like using commas for periods) that forces you to use a semi-colon in DAX.

Our code was American code

[PaulDBrown]

@dedelman_clng

Good one! that was indeed the culprit in syntax error...

However, the computed result comes up with the following:

Is it due to the fact that there are many more columns in the actual table??

Thanks a lot!

Paul

[dedelman_clng]

@PaulDBrown - now that I see the screenshot of the actual data we need to take a different approach.  I thought this whole time you already had the data pivoted/summarized by year.  VALUES will be returning many rows instead of a single value.

I think it should be as easy as changing VALUES to SUM (bolded below).  Try that while I try to mock up some data and try it on my end.

CustomerType = 
var TYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]), Tab[Year]=Year(TODAY())))
var LYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]),  Tab[Year]=YEAR(TODAY())-1))
return
IF (AND( TYS = 0, LYS > 0), "LOST",
    IF(AND(TYS <> 0, LYS <> 0), "REPEAT",
       IF(AND(TYS <> 0, LYS = 0), "NEW", BLANK()
       )
    )
)

[PaulDBrown]

@dedelman_clng

David, That makes sense...

I tried your suggestion in the model and it "worked" so to speak. Let me explain...

I've stumbled across an issue which actually means (I think) that the solution is not possible. In practice, the model will only of course include data of campaigns aired. ie, if a client did not invest in March 2016, there will be no record of it of course. There will be no record of clients which for example didn't  invest in March 2016. Therefore the solution will never find "new" or "lost" clients, since when the variable LYS (or TYS) is applied it finds no records for clients with 0 investment. A bummer...

So I guess I'm back to the beginning with this...

[dedelman_clng]

@PaulDBrown

I mocked up some detail data, it appears that it is working from my end.  Perhaps my detail was too simplified?

CustomerType = 
var TYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]), YEAR(Tab[Date])=Year(TODAY())))
var LYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]),  YEAR(Tab[Date])=YEAR(TODAY())-1))
return
IF (AND( TYS = 0, LYS > 0), "LOST",
    IF(AND(TYS <> 0, LYS <> 0), "REPEAT",
       IF(AND(TYS <> 0, LYS = 0), "NEW", BLANK()
       )
    )
)

David

[PaulDBrown]

@dedelman_clng

HI David,

Not sure why, but in my data set the whole column returns the same value: "REPEAT"...I need to invest some time to try to work it out....

Thank you for your help on this!

Paul.

[dedelman_clng]

Hi @PaulDBrown - I neglected to mention that I also added YEAR in the date comparison (highlighted below).  Sorry for the oversight.

CustomerType = 
var TYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]), YEAR(Tab[Date])=Year(TODAY())))
var LYS = calculate(SUM(Tab[Sales]), filter(ALLEXCEPT(Tab, Tab[Client]),  YEAR(Tab[Date])=YEAR(TODAY())-1))
return
IF (AND( TYS = 0, LYS > 0), "LOST",
    IF(AND(TYS <> 0, LYS <> 0), "REPEAT",
       IF(AND(TYS <> 0, LYS = 0), "NEW", BLANK()
       )
    )
)

[PaulDBrown · Accepted solution]

@dedelman_clng @nairisha

David,

Thank you, I did try with the corrected YEAR. What I'm seeing is a single value of "REPEAT" along all lines (this is not surprising when I try the whole current year as per your formula, since my data set only has repeating clients this current year; I'm going to have to change some rows manually to force the data to deliver different outputs and see if it works.

However there are "New" and "Lost" clients month by month, and I've tried using the formula including:

YEAR(Tab[Date])=Year(TODAY()) && MONTH(Tab[Date] = MONTH(TODAY())

YEAR(Tab[Date])=Year(TODAY())-1 && MONTH(Tab[Date] = MONTH(TODAY())

for the current month but it also delivers "Repeat" as a unique value across all rows, when there are other types of clients in the current month. So I'm going to have to work deep to find out what is going on.

BUT there is a bright side to all of this! I subsequently found a workaround (I'm kicking myself for not having thought of this before).... In the report I'm working on for this model, it occured to me to filter the visual displaying columns for [client], [this year sales], [last year sales] and [% difference between both years].

So basically what I've done is (using the Visual Filters and filtering by the % difference column which is a measure using the DIVIDE function):

REPEAT;  when % DIFF IS NOT BLANK AND IS NOT -100% (-100% is the value computed when there is a sales value in last year and no result this year)

NEW: when the % DIFF IS BLANK (BLANK is computed when the denominator is 0 within the DIVIDE function)

LOST: when the % DIFF = -100%

and there you go!!

Not ideal, since it means I have to repeat the process every time I want to do any kind of analyses based on client status, but at least I've got it working!

Thank you again for all your help!!

Best regards,

Paul.

[ivancespedesl]

Hello,

I have this same problem but my variable is S and N. I have many transversal cuts of my users, and I need to understand if some of then change status from S to N and viceversa. How could i do this?

Thanks in advance,

IC