cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ankibano
Frequent Visitor

Calculation of Lost Customers Vs New Customer in same graph

Hello !

 

After reading many blogs and tried many calculation methods I am looking for a solutions to compare the turnover value for lost customers and new customers.

 

Condition for Lost Customers: 

if current year ytd = 0 and prev full year > 0 then Lost Customer current year = prev year turnover

 

Condition for New Customers:

 

IF current year ytd > 0 AND  prev full year = 0 THEN new customers current year = current year ytd turnover

 

And compare the value for each year (lets now for example 2021 )

 

I have attached a demo of output graph I am looking for.

 

The structure of the raw is something like this :

 

Data Structure.png

 

Needed Graphs :

1. Lost Customer Graph :

 

LostCustomers.png

 

2. Lost Vs New customer graph

 

 

LostVsNew_Customers.png

 

3. New Customer Graph:

 

New_Customers.png

 

Any suggestion would be really appreciated.

 

Thanks !

1 ACCEPTED SOLUTION

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

9 REPLIES 9
Jihwan_Kim
Community Champion
Community Champion

Hi, @ankibano 

 

Please check the sample pbix file's link down below.

I created it today for another topic but I think it is related.

All measures are in the sample pbix file.

Sorry to say that it is quite difficult to create sample data based on your inquiries.

If you have sample data already, please share the link here, then I can try to look into it.

And I hope the below sample pbix file can help a little bit.

 

https://www.dropbox.com/s/klfq0dyai1gnonp/cust.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Hi @Jihwan_Kim ,

 

You can download the sample file from below link :

 

https://drive.google.com/drive/folders/1MFRmKzqo9byLA_6QkPl3LBAD91otY5IF?usp=sharing

 

Thanks

Hi,

You may download my PBI file from here.

Hope this helps.

Untitled.png


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

View solution in original post

Thank you @Ashish_Mathur , 

This was the expected solution.

 

 

You are welcome.


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

Hi,

please check the below link.

 

https://www.dropbox.com/s/tj11xv5m5fogpaq/Ankibano.pbix?dl=0 

 

I am not sure if I used the sample correctly, but when I saw the data, I think there were no lost customers in 2021.

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Linkedin: https://www.linkedin.com/in/jihwankim1975/


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.


Best regards, JiHwan Kim


Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

vanessafvg
Super User I
Super User I

can you provide sample data in text format?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Hi @Jihwan_Kim ,

 

Thank you for your inputs. But unfortunately, the result presented in the PBI file is not the one I am looking for. I am breaking the requirements in the below points :

  1. The full turnover value of 2020 for Customer 1 and Customer 2, should be presented in  Jan 2021 value because Customer 1 and Customer 2 Jan 2021 value is 0 or blank.
  2. As customer 1 already have value in Feb 2021, so the Lost customer value in feb 2021  = (2020 Total Turnover value ) – ( Customer 1  2020 turnover Value).
  3.  

Hi @vanessafvg ,

 

You can download the file from the below link :

 

https://drive.google.com/drive/folders/1MFRmKzqo9byLA_6QkPl3LBAD91otY5IF?usp=sharing

 

Thanks

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors