Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi, @Anonymous 

 

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Anonymous
Not applicable

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/
Anonymous
Not applicable

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/

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 accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


vanessafvg
Super User
Super User

can you provide sample data in text format?





If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!




Anonymous
Not applicable

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.  
Anonymous
Not applicable

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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