cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Responsive Resident

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

 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

13 REPLIES 13
Community Support

Hi, @vs_7

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.

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.

Responsive Resident

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

Super User

Hi,

Regards,
Ashish Mathur
http://www.ashishmathur.com
MVP

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

Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

Responsive Resident

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)

MVP

Hi @vs_7 ,

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

Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

Responsive Resident

Hi @mwegener ,

please find the attached Screenshot of model view

MVP

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

Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

Responsive Resident

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

MVP

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.

Please mark my post as solution, this will also help others.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."

Responsive Resident

Hi @mwegener ,

Any alternative solution for the issue.

Super User

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

Responsive Resident

Hi @Ritaf1983 ,

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

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors