cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ManjunathaEP
Helper I
Helper I

NELC Analysis

Hi, Thank you in advance for supporting the DAX for the below sample data.

I would like to get DAX for NELC (New, Expand,Lost, Churn) analysis for Revenue and customer count.

 

ManjunathaEP_1-1618491562078.png

 

Expected results are calculated below for 2019:

New = Customer ABC (count=1) is returned as New in 2019 with an Amount of 67

Expand = Customer YRP (count=1) revenue is increased to 67 in 2019 from 45 of 2018,so expand revenue is 22

Lost = Customer PS and MEP (count=2) revenue in 2019 is 0, hence lost revenue is -1135

Churn = Customer HM (count=1) revenue is decreased to 567 in 2019 from 876 of 2018, so churn revenue is -309

 

8 REPLIES 8
daxer
Solution Sage
Solution Sage

@ManjunathaEP 

 

Here's a full article that does in an expert way all you need: Calculate New, Returning, Lost, and Recovered Customers in #dax - SQLBI

Jihwan_Kim
Community Champion
Community Champion

Hi, @ManjunathaEP 

 

Please check the below picture and the sample pbix file's link down below whether it is what you are looking for.

 

all measures are in the sample pbix file.

 

Picture8.png

 

https://www.dropbox.com/s/gpftz39v7a8r76z/manjunathaep.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, Thank you for sharing the .pbix on expected DAX, it is working fine by year.. Is possible to get the results for different selections like Revenue class or by customer wise, country wise etc.,

 

Thank you again

 

 

Hi, @ManjunathaEP 
Thank you for your feedback.

I think it is possible.

Please share your sample case and which dimension you want to add, then I can try to have a look into it.

 

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, Thank you again for helping on this.

With reference to the same sample data, I would like to get the Sales revenue by customer wise like below:

ManjunathaEP_0-1618570884870.png

Please do the needful

 

 

Hi, @ManjunathaEP 

I realized that there was some mistake in my file.

I fixed it and please check the below link. I added the table visualization that you requested.

 

thank you.

 

https://www.dropbox.com/s/gpftz39v7a8r76z/manjunathaep.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, sorry to bother u again, the sent DAX are working fine in the sample data but when I replace it with the original data, it is calculating the total summary instead of individual values for customers. Ex: Below screenshot is for lost Sales 

ManjunathaEP_0-1618579068550.png

Thank you

 

Hi, @ManjunathaEP 

 

If it is OK with you, please share your sample pbix file's link, then I can try to have a look into it to come up with a solution.

 

Thank you.


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

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