Missing values from sameperiodlastyear

[JamesLT]

Hello everyone, I am new to DAX.

This is what I got...

Shop_Name	Season	Sales Net MTD	Sales Net MTD LY
ABC	Current Season	158,732​	63,827​
	Last Season	1,874​
	Next Season	269,274​	48,538​
	Off Season	4,482​
ABC Total		434,362​	114,588​

This is what I want...

Shop_Name	Season	Sales Net MTD	Sales Net MTD LY
ABC	Current Season	158,732	63,827
	Last Season	1,874
	Next Season	269,274	48,538
	Off Season	4,482
	Other-COT		2,223
ABC Total		434,362​	114,588​

The problem I have now is missing "Other-COT" sales from MTD LY. Everything is good (including the shop total) except "Other-COT" is not showing in my pivot table.

I guess the reason is MTD data has no Other-COT sales for shop ABC and MTD LY is bounded by MTD's season filter.

Highly appreciate if someone could help me or could give me a clue.

Below are my formulas,

Sales Net = sum(SALES[NetSales])

Sales Net MTD = CALCULATE([Sales Net],DATESMTD('Calendar'[Date]))

Sales Net MTD LY = CALCULATE([Sales Net MTD],SAMEPERIODLASTYEAR('Calendar'[Date]),FILTER(all(SALES),SALES[Date]>=STARTOFMONTH('Calendar'[Date])&&SALES[Date]<=DATEADD(STARTOFMONTH(SALES[Date]),DAY(MAX(SALES[Date]))-1,DAY)),KEEPFILTERS(Season_Master),KEEPFILTERS(Shop_Master))

And I have 2 slicers selecting Year and Month.

Million Thanks
James