Sales Potential and new customers

[enexius]

Hi everyone,

I've the following task at hand, but couldn't figure out a solution yet.

I'd like to calculate two things:

1. Potential of Sales for a specific year (e.g. 2022). Potential is defined as: "Customer has a contract(s) in the year prior to the selected year, but not in the selected year (furthermore defined by the state of the contract = valid) or after." In the upcoming example this would be: Potential for 2022 = 15$ (Customer 2 has a contract for 2022 but it isn't valid)

One solution I found was to pivot the Contract year with the value, where i defined a measure like this:

Spoiler

Potential 2022 = 
CALCULATE(SUM(Potential[2021]),

Potential[2022]=BLANK(),

Potential[2023]=BLANK(),

Potential[2024]=BLANK(),

'Potential'[State of Contract]<>"expired",
'Potential'[State of Contract]<>"unknown")

but that didn't help with the second task.

2. Sales for new customers: Customer who didn't have contracts three years prior to the selected year. In the example below (for 2023) this would be 10$ (Customer 5 has no contract from 2020-2022).

I hope there is a solution to this. 

Department	Customer	Order Date	Process	Contract State	Position	Contract Year	Value
A	1	01.01.2021	3	valid	1	2022	10$
A	1	01.01.2021	3	valid	1	2023	10$
A	1	01.01.2021	3	valid	1	2024	20$
A	1	02.01.2021	4	expired	1	2025	30$
A	2	04.05.2019	3	valid	1	2021	10$
A	2	04.05.2019	3	valid	2	2021	5$
A	2	10.12.2019	6	expired	1	2022	7$
B	3	12.12.2020	9	valid	2	2022	8$
A	2	08.01.2020	12	unknown	2	2022	10$
C	4	10.12.2020	7	valid	1	2021	15$
A	4	05.07.2020	5	valid	1	2022	13$
C	4	03.01.2019	1	valid	1	2022	12$
B	5	02.03.2020	18	valid	2	2023	10$

Thank you in advance for your help!

Cheers,

Enno

[plainly · Accepted solution]

Hello,

Here is one solution with DAX (edited 25.2.2021). I think having a static field telling who is a new customer is valuable for many type of customer analytics. 

See sample file: https://drive.google.com/file/d/1_tavilUUe9PSoE2PYlF9mKEOCZW4LghY/view?usp=sharing

Below 2023 is the reporting year. The logic is that we need to know the previous contract year a customed had a contract before the reporting year. Then, customer is new if this year is 3 years away or if this year is not available.

2023 is replacable with a VAR you define or for example YEAR(today())

NEW COLUMNS:
1)
customer_last_contract_year = CALCULATE(Max(sales_customers[Contract Year]),ALLEXCEPT(sales_customers, sales_customers[Customer]))


2)
customer_prev_contract_year_before_reporting = CALCULATE(CALCULATE(MAX(sales_customers[Contract Year]),FILTER(sales_customers,2023>sales_customers[Contract Year])),ALLEXCEPT(sales_customers,sales_customers[Customer]))

3)
New_customer = IF(sales_customers[customer_last_contract_year]>= 2023 && ( ISBLANK(sales_customers[customer_prev_contract_year_before_reporting]) || (2023-sales_customers[customer_prev_contract_year_before_reporting] >= 3 && NOT ISBLANK(sales_customers[customer_prev_contract_year_before_reporting]))),TRUE,FALSE)

SALES VALUE NEW CUSTOMERS:
New_Customer_Sales = CALCULATE(SUM(sales_customers[Value]), sales_customers[new_customer] = TRUE)

Typically I would do this for a client with SQL or Power Query, with similar steps (SQL window function would help here too)

Check my Blog for more date calculation / what to use and when:

https://www.plainlyresults.com/blog/power-bi-dax-how-to-calculate-and-filter-based-on-dates/

https://www.plainlyresults.com/blog/when-to-use-dax-measure-vs-calculated-column-vs-other-tools/

[Icey · Accepted solution]

Hi @enexius ,

Please check the attached file,

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[enexius]

@Icey: Thank you for your solution, this was a very well described example and worked flawlessly! Also your data set showed me some improvments in my table structure, which also helped performance-wise.

[plainly · Accepted solution]

Hello,

Here is one solution with DAX (edited 25.2.2021). I think having a static field telling who is a new customer is valuable for many type of customer analytics. 

See sample file: https://drive.google.com/file/d/1_tavilUUe9PSoE2PYlF9mKEOCZW4LghY/view?usp=sharing

Below 2023 is the reporting year. The logic is that we need to know the previous contract year a customed had a contract before the reporting year. Then, customer is new if this year is 3 years away or if this year is not available.

2023 is replacable with a VAR you define or for example YEAR(today())

NEW COLUMNS:
1)
customer_last_contract_year = CALCULATE(Max(sales_customers[Contract Year]),ALLEXCEPT(sales_customers, sales_customers[Customer]))


2)
customer_prev_contract_year_before_reporting = CALCULATE(CALCULATE(MAX(sales_customers[Contract Year]),FILTER(sales_customers,2023>sales_customers[Contract Year])),ALLEXCEPT(sales_customers,sales_customers[Customer]))

3)
New_customer = IF(sales_customers[customer_last_contract_year]>= 2023 && ( ISBLANK(sales_customers[customer_prev_contract_year_before_reporting]) || (2023-sales_customers[customer_prev_contract_year_before_reporting] >= 3 && NOT ISBLANK(sales_customers[customer_prev_contract_year_before_reporting]))),TRUE,FALSE)

SALES VALUE NEW CUSTOMERS:
New_Customer_Sales = CALCULATE(SUM(sales_customers[Value]), sales_customers[new_customer] = TRUE)

Typically I would do this for a client with SQL or Power Query, with similar steps (SQL window function would help here too)

Check my Blog for more date calculation / what to use and when:

https://www.plainlyresults.com/blog/power-bi-dax-how-to-calculate-and-filter-based-on-dates/

https://www.plainlyresults.com/blog/when-to-use-dax-measure-vs-calculated-column-vs-other-tools/

[enexius]

Hi @plainly i finally found the time to test your solution. It solved my problem and with a few minor tweaks I was able to adapt your formula to fit my viewer-enabled slicing (They can choose which contract year they want to look at).

So thank you very much!

[amitchandak]

@enexius , These blogs provide ways to get new customers, See if they can help with some modification

Customer Retention Part 1:
https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-1-Month-on-Month-Retention/b...
Customer Retention Part 2: Period over Period Retention :https://community.powerbi.com/t5/Community-Blog/Customer-Retention-Part-2-Period-over-Period-Retenti...

[enexius]

Hi @amitchandak ,

thank you for your reply. I looked through your blog post and understood, that I could divide some dimensions into different tables, as well as look into customer sales based on "order date". My problem with that, is that the order date for me is irrelevant for dividing customers into existing and new customers, and furthermore exlcude certain contract states.

My seperation (New/existing Customer) is based on wether a customer has a contract for Year X (2021) and doesn't have a valid contract three years prior to that.

I'm very sorry if I just couldn't put 2 + 2 together with the help of your blog, but maybe you could push me in the right direction again.

Cheers,

Enno

[amitchandak]

@enexius , Try a measure like 

measure =
var _1 = calculate(min('Table'[Year]), filter( ALLSELECTED ('Table' ),'Table'[Customer] <MAX ('Table'[Customer] ) && 'Table'[Contract] = "Valid"))
return
if( isblank( _1) || _1 < max('Table'[Year]) -2 , "New", "repeat")