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 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.
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 روز مراجعه نکنه بهش میگن از دست رفته ؟ میدونم شاید درخواستم نوعی سوء استفاده به نظر بیاد اما اگر بتونید فرمول ریاضی این موارد رو بگید میتونیم با کد نویسی پیاده سازیش کنیم
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 :-)
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.
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])
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 ?
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 😭
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') )
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.
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.
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.
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
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.
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.