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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Advocate II
Advocate II

Lost customers no purchase more than 6 months

I tried all possible solutions and tried techniques suggeste, but still cant ge through this.


All im tyring to do is show the lost customer sales from previous year, who didnt purchase for 6 months from selected month. Group by customer and Item.


I created 3 measures, All works except the total. It seems to consider total onlly for current year or not at all.

Last period sales = 


prior year sales =

    -Sales[SALES REVENUE_PY_FxAdj],
    DATEADD ( 'Calendar'[Date], -1, YEAR )

lost customer sales = If last period sales is less than or equal to 0 or blank, return the prior year sales for that month in negative.

IF(OR(ISBLANK([Last Period Sales]),[Last Period Sales]<=0), [Prior Year Sales],BLANK()) 

I am seeing right values for the lost customer sales but total is blank.

I tried all possible SUMX, SUMMARIZE, still it doesnt work.


At the end I want to be able to see the total for each month in the below table


Please can someone help me.

 @OwenAuger  @MFelix  @Zubair_Muhammad Please can you help

Super User
Super User

Hi @EV ,


Without the detailed data is difficult to give you a correct result however believe that you need to create a SUMX based on a virtual table to make the calculation for total.


Can you share some sample data or mockup file?


If information is sensitive please share it trough private message.





Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

@MFelix  Thank you for your response, sent you private message

Super User
Super User

Can you check if don't summarize is check for that measure?  If so change it to sum.

Screenshot 2019-09-07 18.01.52.png

Thank you for your respons. Its a measure so i dont see that option dont summarize or sum 


no sum.PNG


You may download my PBI file from here.  For the chosen items in the slicer, there are customers in January and March (1 each) who did not record any net revenue for 6 months ended January and March.

Hope this helps.


Ashish Mathur

Thank you so much for looking into this. 


I am trying to get the total in the Lost sales, right now, it just shows blank although there are values in the column.


But the same measure total shows if I have bring the prior year revenue for customers and item in Matrix and apply visual filter with Last period revenue <=0 

Im trying to make the total work for Lost measure and be able to see the total alone for all months. could you please check again?



I do not understand.  In my screenshot, there is 1 customer in January who did not record any sales for the 6 months ended January.  If the Company has not recorded any sales for 6 months ended January, then how can one compute lost sales?  Also, i 'd like to solve the problem with measures that i am writing (rather than refer to the formulas that you hve written).  It will be ideal to share a much smaller dataset and show me the exact expected result on that dataset so that i can tally my answers with your before sharing the file with you.

Ashish Mathur

@EV There is great post on lost customers, check here.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Thank You, I went through his articles as well, the catch is to display previoue year sales for that lost customer.


Everything works,able to calculate  purchases made in last months and able to check if the customer bought items in the last 6 months, then its not lost, if the customer did not buy or bought but its less than or equal to 0, then its lost customers. For lost customers display their last year sales.


I am able to display last year sales however the grand total is zero. but I am seeing correct sales for each row. I tried SUMX but that doesnt work either, Am missing something very obvious that I am not able to spot or may there is a better way to do this 😞 

Super User
Super User


Share some data in a form that can be pasted in an Excel workbook.  On the sample dataset, please also show the expected result.

Ashish Mathur

Helpful resources

Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City


Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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