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.

[PaulDBrown]

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

[dedelman_clng]

@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

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

[dedelman_clng]

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

[PaulDBrown]

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.