Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
vs_7
Responsive Resident
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

PARTNERCODEAmount
30AAD014001.53
30AAG033001.38
30AAN0120052.56
30AAR0320046.51
30AB0122003.42
30ABM041009.52
30ABS0220090.17
30ACC012003.02
30ACC122007.31
30ACE041003.29
30ACEL440012.34
30ACP0330047.00
30AD0A11007.57
30ADI0220728.37
30ADI034017.98
30ADS011001.17
30ADV4130012.38
30AEC0110030.97
30AEG0GUJ07.78

 

@amitchandakplease suggest

13 REPLIES 13
v-yilong-msft
Community Support
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.

vyilongmsft_0-1704262231545.png

 

  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.

vyilongmsft_1-1704262231549.png

 

 

 

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.

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

Hi,

Share the download link of the PBI file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

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."
twitter - LinkedIn - YouTube - website - podcast


vs_7
Responsive Resident
Responsive Resident

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)

Hi @vs_7 ,

 

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

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

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."
twitter - LinkedIn - YouTube - website - podcast


vs_7
Responsive Resident
Responsive Resident

Hi @mwegener ,

please find the attached Screenshot of model view
 

vs_7_0-1704277479492.png

 

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

 

 

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

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."
twitter - LinkedIn - YouTube - website - podcast


vs_7
Responsive Resident
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

vs_7_0-1704341076512.png

 

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.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

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."
twitter - LinkedIn - YouTube - website - podcast


vs_7
Responsive Resident
Responsive Resident

Hi @mwegener ,

Any alternative solution for the issue.

Ritaf1983
Super User
Super User

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

vs_7
Responsive Resident
Responsive Resident

Hi @Ritaf1983 ,

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

 

please suggest?

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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