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 ,

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

     

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

      Hi Anonymous, I just saw that the sales table is in DirectQuery mode, so the data source probably has problems understanding the DAX statement.

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi mwegener ,

        Any alternative solution for the issue.