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 Cust...
Need Help
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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi mwegener ,

      please find the attached Screenshot of model view
       

       

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

        Hi Anonymous ,

        I would perform the customer count on the sales table.

        The code should then look something like this.

        The calendar table must be marked as a date table for the time logic.

        https://learn.microsoft.com/en-us/power-bi/transform-model/desktop-date-tables?WT.mc_id=DP-MVP-5004288#benefits-of-setting-your-own-date-table

         

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

         

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