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 (
-Sales[SALES REVENUE_PY_FxAdj],
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.
