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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
powerbiexpert22
Post Partisan
Post Partisan

lost customers

the lost customers should show 0 it is showing 4 as below highlighted, i have created 3 measures to calculate lost customers.

lost customers are those customers who have not done any purcase last 2 months

 

please see sample pbix file:

https://drive.google.com/file/d/1d0xhBx_VIL-Z9XRR5TaJIKwaGUGK8FqK/view?usp=drive_link

powerbiexpert22_0-1720544757175.png

 

 

 

 

Lost Customers Logic:

 

saleslast2months =
CALCULATE(Sales[salesamount],
DATESINPERIOD('Date'[Date],LASTDATE('Date'[Date]),-2,MONTH))
 
lostcustomer_flag =
IF(ISBLANK(Sales[saleslast2months]),"Lost",Sales[salesamount])
 
LostCustomers =
CALCULATE(
    DISTINCTCOUNT(Customers[customer_id]),
    FILTER(Customers,[lostcustomer_flag]="Lost"))
 
 
1 ACCEPTED SOLUTION
NaveenGandhi
Super User
Super User

Hi @powerbiexpert22 

The output seems to be working as per the logic[No purchase in last 2 month], as of 12-01-2024 c1 has made a purchase while other 4 has not. 

Can you explain the requirement a bit more elaborate?

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!


LinkedIn

View solution in original post

6 REPLIES 6
NaveenGandhi
Super User
Super User

Hi @powerbiexpert22 

Check the below solution, I have created a calculated column.

Let me know if this works.

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!


LinkedIn

Hi @NaveenGandhi 

 

I see below error, can you explain what logic you have written?

 

powerbiexpert22_0-1720660280473.png

 

@powerbiexpert22 

 

Look for lost column on sales table, you can neglect the summarize table

 

Regards,

Ng

powerbiexpert22
Post Partisan
Post Partisan

Hi @AnkitaaMishra , @NaveenGandhi 

 

Lost customers are those customers who placed orders before last 2 months from the specified date however they did not place any order within last two months from the specified date , below is the explanation based on the data 

DateLost Customer CountReason
12-01-20240There are no customers who placed any order before 12-Nov-2023
14-02-20240There are no customers who placed any order before 14-Dec-2023
15-02-20240There are no customers who placed any order before 15-Dec -2023
16-03-20240There is 1 customer who placed orders before 16-Jan-2024 i.e C1 on 12th Jan 2024 but he placed another order on 14th Feb 2024 so he is not the lost customer
26-04-20242There are 2 customers who placed orders before 26-Feb-2024 i.e. C1,C2 and they have not placed any order between 26-Feb-2024 and 26-Apr-2024 so these two are lost customers
28-04-20242There are 2 customers who placed orders before 28-Feb-2024 i.e. C1,C2 and they have not placed any order between 28-Feb-2024 and 28-Apr-2024 so these two are lost customers
31-05-20243There are 3 customers who placed orders before 31-Mar-2024 i.e. C1,C2,C3 and they have not placed any order between 31-Mar-2024 and 31-May-2024 so these three are lost customers

 

AnkitaaMishra
Resolver I
Resolver I

Hi @powerbiexpert22 ,
Could you please explain the requirement again and what are your expected output for each date as the DAX looks fine.

ankita_mishra_9_0-1720546688978.png

 



NaveenGandhi
Super User
Super User

Hi @powerbiexpert22 

The output seems to be working as per the logic[No purchase in last 2 month], as of 12-01-2024 c1 has made a purchase while other 4 has not. 

Can you explain the requirement a bit more elaborate?

If this post helps, then please consider Accept it as the solution to help the others find it more quickly. Appreciate you kudos!!

Follow me on LinkedIn!!!


LinkedIn

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

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