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() ) ) )
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
in the first line (I get an error saying and EoF is expected..)
Apologies again since I'm very new to all of this..
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...
@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
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!
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 - 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() ) ) )
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...