Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
BXL
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 BI query.JPG

1 ACCEPTED SOLUTION
v-caliao-msft
Employee
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)

Capture.PNG

 

Regards,

Charlie Liao

 

 

View solution in original post

3 REPLIES 3
v-caliao-msft
Employee
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)

Capture.PNG

 

Regards,

Charlie Liao

 

 

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
Frequent Visitor

Sorry Charlie,

 

I spoke too soon.....

 

Your solution worked fine... Smiley Very Happy

 

 

thanks for your help

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.