Calculated Filed to identify repeating/new clients

[BXL]

Hi guys,

Would appreciate some's help with this;

I have a SALE Table and a Calendar Table with Financial Years ending June. I would like tp categorise clients in the Sales table as "New" or "Exisitng". If a client was invoices in the previous financial year then its flagged as "Exisiting". If a client was not invoiced in the previous finacial year then its a "New Client" even if the client happend to have been invpoiced many years ago, its the last fiaincial year that counts.

Then, I'd like to do a Measure that calculates New Client Sales and Existing Client Sales in a particular financial year.

See attached

[v-caliao-msft · Accepted solution]

@BXL,

Create a calculated column and 3 calculated measures like below.
Financial Year = IF(MONTH(Sales[Date])>6,YEAR(Sales[Date])+1,YEAR(Sales[Date]))

Client Type =
var CurrentFY = MAX(Sales[Financial Year])
var PreviourFY = CurrentFY-1
var CurrentCient = MAX(Sales[Clietn])
return IF(ISBLANK(CALCULATE(SUM(Sales[SaleAmount]),FILTER(ALLEXCEPT(Sales,Sales[Clietn]),Sales[Financial Year]=PreviourFY))),"New Client","Existing Client")

Sales(NewClients) =
var CurrentFY = MAX(Sales[Financial Year])
var CurrentFYSalesAmount = CALCULATE(SUM(Sales[SaleAmount]),FILTER(ALLEXCEPT(Sales,Sales[Clietn]),Sales[Financial Year]=CurrentFY))
return if(Sales[Client Type]="New Client",CurrentFYSalesAmount,BLANK())

Sales(ExistingClients) =
var CurrentFY = MAX(Sales[Financial Year])
var CurrentFYSalesAmount = CALCULATE(SUM(Sales[SaleAmount]),FILTER(ALLEXCEPT(Sales,Sales[Clietn]),Sales[Financial Year]=CurrentFY))
return if(Sales[Client Type]="New Client",BLANK(),CurrentFYSalesAmount)

Regards,

Charlie Liao

[BXL]

thanks Charlie,

we're very close!!!

your Measures did produce the correct row results but for some reason the total of the column, regardless of which Financial Year/s I filter, always produces the total Sale(NewClient) and Sales(ExistingClient) for the most reacent Financial Year in the database.

would be great if this could be overcome 🙂

[BXL]

Sorry Charlie,

I spoke too soon.....

Your solution worked fine...

thanks for your help