Partner Count

[vs_7]

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

PARTNERCODE	Amount
30AAD01400	1.53
30AAG03300	1.38
30AAN01200	52.56
30AAR03200	46.51
30AB012200	3.42
30ABM04100	9.52
30ABS02200	90.17
30ACC01200	3.02
30ACC12200	7.31
30ACE04100	3.29
30ACEL4400	12.34
30ACP03300	47.00
30AD0A1100	7.57
30ADI02207	28.37
30ADI03401	7.98
30ADS01100	1.17
30ADV41300	12.38
30AEC01100	30.97
30AEG0GUJ0	7.78

@amitchandakplease suggest

[v-yilong-msft]

Hi, @vs_7 

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

1. First you can create a table.

2. 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"))

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

[vs_7]

Hi @v-yilong-msft 
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

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[mwegener]

Hi @vs_7, 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

[vs_7]

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]

Hi @vs_7 ,

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

[vs_7]

Hi @mwegener ,

please find the attached Screenshot of model view
 

[mwegener]

Hi @vs_7 ,

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

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

[vs_7]

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]

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

[vs_7]

Hi @mwegener ,

Any alternative solution for the issue.

[Ritaf1983]

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

[vs_7]

Hi @Ritaf1983 ,

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

please suggest?