I think this was one of the most useful and to the point videos on power bi. You always rock. I have learned power bi by watching your videos and reading your posts.
Ideally this is a wrong assumption because we are calculating last date of each customer, suppose a customer bought from us in the year 2005 this formula would calculate last date in this customers context and will give the results, however, the starting date should be current date always which would be today or the maximum date in current data set.
Really nice and clearly explained! You might consider using different period for lost and new customers though. For example we consider a customer 'lost' if he didn't order in 12 months, but consider a customer 'new' if he has ordered in the last 3 months and not before. I'm also curious about handling totals :-)
Please tell me how to count the lost customers in this case. I have done the same and now want to show the total number of lost customers in card visual. I need your support, because its a must requirement for me.
سلام روز بخیر خیلی ممنون بابت ویدئوی خوبتون ، من یه loyalty program دارم که برای بیزینس های مختلف پیاده سازی میکنیم در قالب یک باشگاه مشتریان ، امکانش هست فرمول مربوط به دسته بندی های زیر رو طوری به من بدید که برای هر نوع کسب و کاری قابل استفاده باشه : 1 - قهرمانان 2 - وفداران 3 - مشتریان نیاز به توجه at risk 4 - مشتریان از دست رفته lost 5 - مشتریان پولدار 6 - مشتریان کم پول چون میانگین مراجعه مشتری ها برای هر کسب و کار بازه های متفاوتی هست ، پس ما نیاز داریم که اول میانگین مراجعات مشتری ها رو در بیاریم و این میانگین باید به ازای هر گروه محصول باشه چون دوره برگشت مشتری در هر گروه محصول هم ممکنه متفاوت باشه. اما نکته اینجاست : فرض کنید میانگین مراجعه هر مشتری در یک گروه کالای بخصوص 30 روزه ، حالا اگه یک مشتری چقدر بیشتر از 30 روز مراجعه نکنه "از دست رفته" محسوب میشه ؟ آیا اگه یک مشتری 31 روز مراجعه نکنه بهش میگن از دست رفته ؟ میدونم شاید درخواستم نوعی سوء استفاده به نظر بیاد اما اگر بتونید فرمول ریاضی این موارد رو بگید میتونیم با کد نویسی پیاده سازیش کنیم
If you're struggling to have the subtotal of lose clients or new clients - You need to create a new table just with the clients (you can use 'group by' on Power Query using your sales table) - Then you need to relationship these 2 tables - Now you will create a measure TOTAL_LOST_CLIENTS = SUMX('YOUR_TABLE_GROUP_BY';YOUR_SALES_TABLE[LOST_CLIENTS_MEASURE])
Excellent! Thanks ! Btw, may i know how to count those lost customers? i've tried this dax Number of lost cust = CALCULATE( COUNTROWS(Sales), FILTER( VALUES(Sales[Customer Name]),[Lost Customer]=1) ) but the value is not accurate.
Hey! Thank u for your video! It’s perfect, only I have a little problem....I want to put in a card the number of new customers “1” that I have in my table.....how I can do it ?? It doesn’t work 😭
Please tell me how I can count the deleted items from last month for each category? I tried to use your example but it seems not working or maybe I do something wrong.
I love your video but when I try and do it, it works BUT I also want to know the Total Lost Customers and in your example and mine that I made the Total Lost Customers is 0 which does not add up to give me the total lost customers ? How do I fix that ?
hello. I been triying to do the last sales formula. it happens that my calendar table has greater dates than de last invoice date so I tried to use last day of invoice table as last date but i cant figure out why it isn't working. The behavior is strange since last date brings older sales than selected period. what can it be the problem? thans best regards.
Hi Raza, How can i rewrite this DAX in better performance , Please help on this. It till execute around 60 seconds, sales table contains 9 crores on records Active Member Base Last 2 Year:= CALCULATE( distinctcount(Sales[Customer]), Sales[New Customer Flag] in {1,0}, DATESBETWEEN( 'Date'[Date], DATEADD(FIRSTDATE('Date'[Date]),-23,MONTH), LASTDATE('Date'[Date]) ), ALL('Date') )
If Im using a date slider as my selection, how do I encorporate the date from this. Does the low and high date on the slider get seen as variables? If I want to look back 12 months from the higher date what do I use.
If you want to do that, there are multiple ways. One is to generate a table (calculated table) with the measure value as a column in that table. then you can use it for filtering. However, that won't be dynamic calculation anymore.
im tring to display the sum of of lost revenue using difference between total and period sum filtering for lost customer, but i cant make it work dax im trying lostrev= calculate([difference total-period]; filter(invoice; lostcustomer=1)) anyone can help?
You can use an iterator. I explained something similar here (for the total count, you can apply similar approach for the total revenue): radacad.com/calculate-totals-in-power-bi-using-iterators-in-dax
Hi Maroun. what do you mean lost customer among all data? you mean someone who has been your customer and not anymore? this ANYMORE means you need to specify a period that says from this point of the time if they are not a customer, then considered as lost. I can't understand the logic that it works without a period!
@@marounsader318 still doesn't make sense. let's say max date in your dataset is 2nd of March 2019. then you want to find out if there is no transaction for clients after that, then they are lost? but there will be no transactions anyway, because that is the last (max) date in your dataset. As I said, you need to find the logic first.
I think this was one of the most useful and to the point videos on power bi. You always rock. I have learned power bi by watching your videos and reading your posts.
Hope to learn more and more PowerBI from your clear presentations. Thank you
Well done, thanks will definitely try with my customers database, but for now I'm still learning the basics
This tutorial has solved many questions. Thanks a lot for doing such good work.
Amazing video and super helpful! One of the best I found. Is there a way to use Lost Customers as a slicer?
Ideally this is a wrong assumption because we are calculating last date of each customer, suppose a customer bought from us in the year 2005 this formula would calculate last date in this customers context and will give the results, however, the starting date should be current date always which would be today or the maximum date in current data set.
I put LASTDATE(ALL(SALES_TABLE[ORDER_DATE])) then works fine (but my company have sales everyday, maybe you can use TODAY())
Really nice and clearly explained! You might consider using different period for lost and new customers though. For example we consider a customer 'lost' if he didn't order in 12 months, but consider a customer 'new' if he has ordered in the last 3 months and not before.
I'm also curious about handling totals :-)
You can do that with two separate parameters for sure :)
thanh you soooo much. This is exactly what I am looking for. Merry Christmas!!!
Thank you very much for this great inspiration. Great quality, great explanation. Thumb up and I will became a loyal subscriber.
Dear Reza, That was really insightful. Thank you for sharing.
Please tell me how to count the lost customers in this case. I have done the same and now want to show the total number of lost customers in card visual. I need your support, because its a must requirement for me.
I have the same doubt... :(
I have the same doubt too
i have the same doubt, anybody got solution please help
@@r.rupaymh I just posted here on the comments mate
سلام روز بخیر
خیلی ممنون بابت ویدئوی خوبتون ، من یه loyalty program دارم که برای بیزینس های مختلف پیاده سازی میکنیم در قالب یک باشگاه مشتریان ، امکانش هست فرمول مربوط به دسته بندی های زیر رو طوری به من بدید که برای هر نوع کسب و کاری قابل استفاده باشه :
1 - قهرمانان
2 - وفداران
3 - مشتریان نیاز به توجه at risk
4 - مشتریان از دست رفته lost
5 - مشتریان پولدار
6 - مشتریان کم پول
چون میانگین مراجعه مشتری ها برای هر کسب و کار بازه های متفاوتی هست ، پس ما نیاز داریم که اول میانگین مراجعات مشتری ها رو در بیاریم و این میانگین باید به ازای هر گروه محصول باشه چون دوره برگشت مشتری در هر گروه محصول هم ممکنه متفاوت باشه.
اما نکته اینجاست : فرض کنید میانگین مراجعه هر مشتری در یک گروه کالای بخصوص 30 روزه ، حالا اگه یک مشتری چقدر بیشتر از 30 روز مراجعه نکنه "از دست رفته" محسوب میشه ؟ آیا اگه یک مشتری 31 روز مراجعه نکنه بهش میگن از دست رفته ؟
میدونم شاید درخواستم نوعی سوء استفاده به نظر بیاد اما اگر بتونید فرمول ریاضی این موارد رو بگید میتونیم با کد نویسی پیاده سازیش کنیم
Thank you Sir, this is a great video, you clear in only 12 minutes questions I had since many days ago
Thank Sir, This has been a very clear presentation.
This is an amazing tutorial. Thanks a lot for sharing.
Thanks Rad, this is very helpful!
How do I create an overview table that sums number of lost customers? and then show a detailed list next to it with who it is.
I just posted here on the comments mate
Simple & effective ...thank you .....is there any way to count those lost customers or sum
Yes there is, you can slightly modify the expression to cover that, I may do another video on that later on
@@RADACAD Please help with that, I really need it!
@@chopan_ss I just posted here on the comments mate
If you're struggling to have the subtotal of lose clients or new clients
- You need to create a new table just with the clients (you can use 'group by' on Power Query using your sales table)
- Then you need to relationship these 2 tables
- Now you will create a measure TOTAL_LOST_CLIENTS = SUMX('YOUR_TABLE_GROUP_BY';YOUR_SALES_TABLE[LOST_CLIENTS_MEASURE])
Hi , when i follow this step all i am getting is the total sales based on the sales table. pls advise
Excellent! Thanks ! Btw, may i know how to count those lost customers? i've tried this dax
Number of lost cust =
CALCULATE(
COUNTROWS(Sales),
FILTER(
VALUES(Sales[Customer Name]),[Lost Customer]=1)
)
but the value is not accurate.
I have done a blog post about that here: radacad.com/calculate-totals-in-power-bi-using-iterators-in-dax
Awesome sir could you please make it on date as well
Amazing video! Many thanks!
Hey! Thank u for your video! It’s perfect, only I have a little problem....I want to put in a card the number of new customers “1” that I have in my table.....how I can do it ?? It doesn’t work 😭
There are different ways to calculate the total in a DAX measure. using Iterators is one of them. I might do a blog/video about it.
RADACAD Thanks you for answer ! Would be perfect watch that vídeo if you do it and its possible ☺️
Excellent and useful! Thank you.
This was a great video!
one drawback, your grand total isn't working, you need to use a sumx to fix that.
Hi Reza, I have one questions about the data model, instead of date if we have a customer id, week number, purchase by week
Awesome !!! Thank you for this.
Please tell me how I can count the deleted items from last month for each category? I tried to use your example but it seems not working or maybe I do something wrong.
I love your video but when I try and do it, it works BUT I also want to know the Total Lost Customers and in your example and mine that I made the Total Lost Customers is 0 which does not add up to give me the total lost customers ? How do I fix that ?
Hi Eric. Will post another blog/video about that :)
@@RADACAD Hi , did you manage to post another Video about this? I have about 40,000 customers to work through and it's difficult without the totals.
@@Alex_Lindley a little bit more complicated, but did you try ... www.daxpatterns.com/new-and-returning-customers/
@@Alex_Lindley I just posted here on the comments mate
hello. I been triying to do the last sales formula. it happens that my calendar table has greater dates than de last invoice date so I tried to use last day of invoice table as last date but i cant figure out why it isn't working.
The behavior is strange since last date brings older sales than selected period. what can it be the problem? thans best regards.
how do you get the dynamic number for lost numbers and new users
Simple and really practical!
Hi Raza,
How can i rewrite this DAX in better performance ,
Please help on this. It till execute around 60 seconds,
sales table contains 9 crores on records
Active Member Base Last 2 Year:=
CALCULATE(
distinctcount(Sales[Customer]),
Sales[New Customer Flag] in {1,0},
DATESBETWEEN(
'Date'[Date],
DATEADD(FIRSTDATE('Date'[Date]),-23,MONTH),
LASTDATE('Date'[Date])
),
ALL('Date')
)
Thank you for this.
VERY NICE VIDEO
Excellent!
If Im using a date slider as my selection, how do I encorporate the date from this. Does the low and high date on the slider get seen as variables? If I want to look back 12 months from the higher date what do I use.
I have created another video about dynamic period calculation in DAX. that will give you how such thing can be implemented.
Thank you very much!
How can you calculate the total lost customers then with that measure?
great, very useful, could have just a counter for lost and won customers
a card value do you know?
Is there a way to make that new customer value a dimension so you can filter on it?
If you want to do that, there are multiple ways. One is to generate a table (calculated table) with the measure value as a column in that table. then you can use it for filtering. However, that won't be dynamic calculation anymore.
Like the basic concept of these logic.
Where can i find the sample data used?
How can we get the total lost customer? The current if statements output is not showing a grand total
check out this video for more measures about it: th-cam.com/video/kSy-7UHe4HE/w-d-xo.html
Interesting, but it would be more useful to have New, Lost or Continuing all in one Colum to use as a filter.
Yes, it just need a new measure with a SWITCH or IF statement in it. I might do a video about it later.
@@RADACAD it will be interesting to get that as a filter, did you manage to make a video about this filter?
im tring to display the sum of of lost revenue using difference between total and period sum filtering for lost customer, but i cant make it work dax im trying lostrev= calculate([difference total-period]; filter(invoice; lostcustomer=1)) anyone can help?
You can use an iterator. I explained something similar here (for the total count, you can apply similar approach for the total revenue): radacad.com/calculate-totals-in-power-bi-using-iterators-in-dax
Sir,How to get total of lost customers? it is 0 why?
I just posted here on the comments mate
Hello Sir
can you please share link to data or order date format.
the dataset is available in the blog post with the link under the video
Awesome
how can we know the lost customers among all the data no i a specific period?
Hi Maroun. what do you mean lost customer among all data? you mean someone who has been your customer and not anymore? this ANYMORE means you need to specify a period that says from this point of the time if they are not a customer, then considered as lost. I can't understand the logic that it works without a period!
@@RADACAD mmm cant we make it read the max date in the dataset and then consider it in the period?
@@marounsader318 still doesn't make sense. let's say max date in your dataset is 2nd of March 2019. then you want to find out if there is no transaction for clients after that, then they are lost? but there will be no transactions anyway, because that is the last (max) date in your dataset. As I said, you need to find the logic first.
@@RADACAD you are right i guess with that logic we can't know our absolute lost customer.
How to calculate retained customers
countx not working