Prior year sales for Lost customers

I studied and went through posts related to this before creating post. Really hope I could get some help. I am definitely missing something very obvious, but I am not able to spot it.

Challenge: I am trying to display prior year sales for lost customers.
Lost Customers= customers who did not buy in the last 6 months +selected month or customers who bought in the last 6 months but the sales amount is less than or equal to 0

created 3 measures, All works except the total. It seems to consider total only 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 (
DATEADD ( 'Calendar'[Date], -1, YEAR )


Lost = SUMX(VALUES('Sales'[Customer]),SUMX(values('Sales'[Item]),IF([Last Period Revenue]<=0 || [Last Period Revenue] =BLANK(),[Prior Year Revenue],0)))

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

I am seeing right values for the lost customer sales but total is incorrect, it seems to miss the rows for prior year sales, but considers the current year rows. In the below screenshot, you could see the red lines amounts are missing, but the blue line

Finally in the summary page I will be displaying the total sales of lost customers in the matrix by month.

Attached my sample file.

Sample mod1.pbix (319.1 KB)

Thank you very much in advance.

Is there any chance you upload some of the sample data excel files? First thing we want to do here is create a dimension table for the Customer and Item and related that your sales table. Never want to filter using your fact tables, so let’s start with getting the data model in better working order. The more time we spend in the data model the easier and faster the DAX becomes.


Thank you for your response Nick, I have added Item and Parent(Customer) table to the power BI.

Please let me know if this helps. Thank you much in advance.
New Sample.pbix (348.0 KB)

I found solution to this. Thank you