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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi mwegener ,
        Thanks for the response, but issue not yet resolved

         

        As per the above measure im getting the below error when i'm adding data to matrix visual with year and values