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
ManjunathaEP
Helper II
Helper II

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

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

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


Go to My LinkedIn Page


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


Go to My LinkedIn Page


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


Go to My LinkedIn Page


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


Go to My LinkedIn Page


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.

Top Solution Authors