Sum of whole previous month, not latest date (month and day) -1 month.

[Wout]

Hi Forum,

I need help.

First some information:

I have the following measurement:

PM1YTDeuro = TOTALMTD(SUM('Sales'[Price]);DATEADD(DATESMTD('sales'[SalesDate]);-1;MONTH))

Sales table:

ID  | Price  | Salesdate 
1     10       01-06-17
2     20       10-06-17
3     100      20-06-17
4     200      08-07-17
5     300      12-07-17

When i run the measurement:

It returns: 30

((ID 1 - 10) + (ID 2 - 20))

But i want the sum of the whole previous month. Not the latest salesdate (12-07-2017) - 1 month. So between 01-06 - 12-06.

Thanks.

[fhill · Accepted solution]

Here you go!

Prior_Month = CALCULATE(SUM(Table2[Price]),            // Sum Price
DATESBETWEEN(Table2[Salesdate],                              // Based on Dates Between
Date(YEAR(EDATE(today(),-1)),MONTH(EDATE(today(),-1)),1),   // Date having Year = Today -1 Month / Month = today -1 Month, and Day = 1
EOMONTH(today(),-1)))                                                 // The End Of Month effective today() -1 month