Forum Discussion

BXL's avatar
BXL
Frequent Visitor
9 years ago
Solved

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 "...
Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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

     

     

v-caliao-msft's avatar
v-caliao-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
BXL
Frequent Visitor
9 years ago

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's avatar
    BXL
    Frequent Visitor
    9 years ago

    Sorry Charlie,

     

    I spoke too soon.....

     

    Your solution worked fine... :smileyvery-happy:

     

     

    thanks for your help