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 "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

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

     

     

3 Replies

  • 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