cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Calculated Filed to identify repeating/new clients

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

1 ACCEPTED SOLUTION
Microsoft Employee

@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

3 REPLIES 3
Microsoft Employee

@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

Frequent Visitor

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 🙂

Frequent Visitor

Sorry Charlie,

I spoke too soon.....

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.