Calculate date value for date before MAX date

[av9]

Hi, In my model I have sales data that is populated for each country at different times of the month. Therefore I can have data for Country 'Belgium' as at '31 Oct' but Norway will be '30 Sep'.

So I use the last sales date to calculate total sales for each country; 

Country Refresh Date = CALCULATE(MAX(Sales[Date]), REMOVEFILTERS(Customer[Customer]))

Sum Sales Amount =

CALCULATE

( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales, (MAX(Sales[Date])=[Country Refresh Date])))

If I want to see the previous months sales data, how do I calculate the prev month based on the date of the last Country Refresh Date per country?

Attached is the PBIX I am using.

https://drive.google.com/file/d/1OvSLP05PXI7ZGEkwYC5KQFJ_9qhbQMaq/view?usp=sharing

[amitchandak · Accepted solution]

@av9 , Try measures like

Sum Sales Amount =
CALCULATE
( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,Sales[Date]=CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country]))))

Last month Sales Amount =
CALCULATE
( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,Sales[Date]=eomonth(CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country])),-1)))

////or

Sum Sales Amount =
CALCULATE
( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,eomonth(Sales[Date],0)=CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country]))))

Last month Sales Amount =
CALCULATE
( SUMX(VALUES(Country[Country]),SUM(Sales[Amount])),FILTER(Sales,eomonth(Sales[Date],0)=eomonth(CALCULATE(max(Sales[Date]),ALLEXCEPT(Country,Country[Country])),-1)))

[av9]

The measures didn't quite work as expected, but it did give the idea to use Eomnth to calculate the previous month from max date.