cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
PaulDBrown Established Member
Established Member

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

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

Client Status YoY.PNG

 

(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.

1 ACCEPTED SOLUTION

Accepted Solutions
PaulDBrown Established Member
Established Member

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

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

23 REPLIES 23
dedelman_clng New Contributor
New Contributor

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

You should be able to do this with measures and IF statements

 

TotSales = calculate(
     sum(table[sales])
)

LYTotSales = calculate (
     sum(table[sales]),
     SAMEPERIODLASYEAR(table[Date of Purchase])

CustomerType = 
IF (AND(TotSales = 0, LYTotSales <> 0), "LOST",
    IF(AND(TotSales <> 0, LYTotSales <> 0), "REPEAT",
       IF(AND(TotSales <> 0, LYTotSales = 0), "NEW", BLANK()
       )
     )
)

Hope this helps

 

David

Highlighted
nairisha Regular Visitor
Regular Visitor

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

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

1.PNG

and Table 2 - Billing

2.PNG

 

 

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.

 

 

3.png

 

Hope this will help!

 

Regards,

Nairisha 

 

PaulDBrown Established Member
Established Member

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

Thank you dedelman_clng,

 

That was really helpful. Sorry is this next question seems basic, but does the "IF Satement" go in a CALCULATED COLUMN in the sales table or is it a separate MEASURE?

 

Paul.

PaulDBrown Established Member
Established Member

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

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:

 

Client Status Data Structure.PNG

 

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.

dedelman_clng New Contributor
New Contributor

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

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

 

Capture.PNG

nairisha Regular Visitor
Regular Visitor

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

Hi @PaulDBrown,

 

How are you determing the status here?

 

Regards,

Nairisha

PaulDBrown Established Member
Established Member

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

@dedelman_clng David, WOW!

 

That was very helpful, though I feel I'm out of my league in this exercise...

 

As regards the this year/last year functionality, I don't suppose adding an expression along the lines of "tab[year]=YEAR(TODAY())" would work?

 

Another concern I'm having is if this calculated column in the context of thousands, if not millions of rows would be a hog and severly affect response times.

 

Many thanks again for you generous help!

 

Paul.

PaulDBrown Established Member
Established Member

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

@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 New Contributor
New Contributor

Re: Client Status Report: "Repating", "New" or "Lost" clients relativ

@PaulDBrown I have to credit @Vvelarde for the code inspiration as an answer to a different post.  I still need to look back through my reference books and understand why it's working instead of just being happy that it does work Smiley Happy

 

Also *headslap* to me for no thinking of something simple like YEAR(TODAY()).  I was focused on using the YEAR value in the data itself and that was causing weird results. Also note that data outside of the two year window will cause things to show blank.

 

If at all possible, you would want to bring as much raw data in PowerBI as possible in order to take full advantage of all of the time intelligence functions that DAX offers.

 

Glad I could help,

David