Lost customers no purchase more than 6 months

[EV]

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 = 

CALCULATE( SUM(Sales[REVENUE_USD_FxAdj]),DATESINPERIOD('Calendar'[Date],LASTDATE('Calendar'[Date]),-7,MONTH)
)

prior year sales =

CALCULATE (
    -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

[MFelix]

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.

Regards,

MFelix

[EV]

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

[amitchandak]

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

[EV]

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

[Ashish_Mathur]

Hi,

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.

[EV]

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?

[Ashish_Mathur]

Hi,

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.

[parry2k]

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

[EV]

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 😞 

[Ashish_Mathur]

Hi,

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