cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
nairisha Regular Visitor
Regular Visitor

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

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

4.PNG

PaulDBrown Established Member
Established Member

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

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

 

 

PaulDBrown Established Member
Established Member

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

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.

dedelman_clng New Contributor
New Contributor

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

@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 Smiley Happy

nairisha Regular Visitor
Regular Visitor

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

Hi @PaulDBrown,

 

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

 

 

 

 

 

Nairisha

PaulDBrown Established Member
Established Member

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

@dedelman_clng

 

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

 

However, the computed result comes up with the following:

 

Client Status.PNG

 

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

 

Thanks a lot!

Paul

PaulDBrown Established Member
Established Member

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

@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 Established Member
Established Member

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

@nairisha

 

Here is the screenshot:

 

Client Status.PNG

 

Thank you for your help!

 

Regards,

Paul.

dedelman_clng New Contributor
New Contributor

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

@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 Established Member
Established Member

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

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