Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Partner Count

Hi All,
I have the below To scenarios to get the partner count.
Scenario 1--> To calculate the count of partners who did not do sales in last year and did sales in the current year are the new Customers.

Scenario 2--> To calculate the count of partners who did sales in last year and did not do sales in the current year are the Lost  Customers.


Data tables -->Sales,Calender table

 

Sample Data

PARTNERCODEAmount
30AAD014001.53
30AAG033001.38
30AAN0120052.56
30AAR0320046.51
30AB0122003.42
30ABM041009.52
30ABS0220090.17
30ACC012003.02
30ACC122007.31
30ACE041003.29
30ACEL440012.34
30ACP0330047.00
30AD0A11007.57
30ADI0220728.37
30ADI034017.98
30ADS011001.17
30ADV4130012.38
30AEC0110030.97
30AEG0GUJ07.78

 

amitchandakplease suggest

Need Help

13 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    2 years ago

    Hi Anonymous, try this

     

    you count the unique customers from the current and previous year and then subtract the unique customers from the current or previous year.
    This should give you the number of customers that did not exist in the previous year or the customers that only existed in the previous year.

     

    new Customers = 
VAR _customersPreviouseYear = CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]), PARALLELPERIOD('Date'[Date],-1,YEAR))
VAR _customersBothPeriods = CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]), UNION(PARALLELPERIOD('Date'[Date],-1,YEAR),PARALLELPERIOD('Date'[Date],0,YEAR)))
RETURN
 _customersBothPeriods - _customersPreviouseYear

     

    lost  Customers = 
VAR _customersThisYear = CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]), PARALLELPERIOD('Date'[Date],0,YEAR))
VAR _customersBothPeriods = CALCULATE(DISTINCTCOUNT(Sales[CustomerKey]), UNION(PARALLELPERIOD('Date'[Date],-1,YEAR),PARALLELPERIOD('Date'[Date],0,YEAR)))
RETURN
 _customersBothPeriods - _customersThisYear

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi mwegener 

      the above calculation is not working and giving error.

      I want calculation on sales of Customercode from customer table

      If customer has done sales in current year and not in last year are new customers and check sales of customer has done sales in last year and not in current year are lost customers


      Tables are Sales table(Net value),Customer table(customer code), Calender Table (year)

      • mwegener's avatar
        mwegener
        Most Valuable Professional
        2 years ago

        Hi Anonymous ,

         

        maybe you can share a screenshot of the model view so I can see the tables, columns and relationships.

  • Ritaf1983's avatar
    Ritaf1983
    Super User
    2 years ago

    Hi Anonymous 
    Can you please include a date column in the sample/share link to pbix?

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Ritaf1983 ,

      I am using year column from calender table and cutomercode from customer master table and sales amount form sales table

       

      please suggest?

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi, Anonymous 

    Based on the information you have provided, Here are my answers to your questions.

    1. First you can create a table.

     

    1. Then create a Measure. This method is designed to display the New Customer and Lost Customer.

    Measure =

         VAR Customer2023 =

              CALCULATE(SUM('Table'[Amount]), FILTER('Table', 'Table'[Year] = 2023))

         VAR Customer2024 =

              CALCULATE(SUM('Table'[Amount]), FILTER('Table', 'Table'[Year] = 2024)

    RETURN

     IF(Customer2024=0&&Customer2023<>0,"Lost",IF(Customer2024<>0&&Customer2023=0,"New"))

     

    1. Finally you get New Customer and Lost Customer.

     

     

     

    How to Get Your Question Answered Quickly 

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

    Best Regards

    Yilong Zhou

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Anonymous 
      Thanks for the response
      But the measure should work dynamicaly for fiscal years
      If i have selected two years then it should the partners count for two years