Month over Month - no previous solutions work!

[dniedrauer]

Previously tried solutions like this one: 

https://community.powerbi.com/t5/Desktop/Calculate-the-month-over-month/td-p/8280

Please see my model linked here: click here

I have two tables, a Calendar Table and an Order Table with Wanted Delivery dates into the future.  

I can't get anything to work. Here's what I've tried so far: 

Sales MTD = calculate('Order Values'[Sales Sum],previousmonth('Date'[Date]))  ---- > returns blank

Sales MTD = calculate('Order Values'[Sales Sum],dateadd('Date'[Date],-1,month)) --- returns the total for the whole data set (4000)

Sales MTD = TOTALMTD('Order Values'[Sales Sum],datesmtd('Date'[Date])) --- > returns blank 

Sales MTD = TOTALMTD('Order Values'[Sales Sum],datesbetween('Date'[Date],date(year(today()),1,1),today())) --- > correct (see below). 

However, trying to shift it back a month does not work: 

LM = calculate([Sales MTD],parallelperiod('Date'[Date],-1,month)) --- > returns current month total. 

LM = calculate([Sales MTD],parallelperiod('Date'[Date],-2,month)) --- > same as previous

LM = calculate([Sales MTD],DATEADD('Date'[Date],-1,month)) --->same as previous

Help!!

[MFelix · Accepted solution]

Hi @dniedrauer,

To what I can understand you want to have a calculation of total values cumulative this can be achieve in 2 ways depending on the scope you want.

If you want to have only YTD you should use the TOTALYTD:

Sales YTD = TOTALYTD([Sales Sum];'Date'[Date])

If you want a month before use this: 

If you want cumulative over all years yous should use the following formula:

Sales cumulative = 
var Select_date = MAX('Date'[Date])
Return
CALCULATE([Sales Sum]; 'Date'[Date] <= Select_date)

I ave change your data and duplicate it for 2017, as you can see below the first formula goes unitl December and returns 4000 and in the second one goes until 8000 without going back to zero:

If you use a slicer then the values on the table and also on visuals or cards can change accordingly.

However if your are only trying to achieve previous month values you should use the MTD as you are using however this is based on context so when you don't have a filter this will give the last month of your data.

Using the following measure you will get the MTD and MTD PM

Sales MTD = TOTALMTD([Sales Sum];'Date'[Date])

Sales MTD PM = TOTALMTD([Sales Sum];DATEADD('Date'[Date];-1;MONTH))

As you can see below in the january the values are blank since you don't have data in December:

 Forgot to mention the measures are based on context so if you aren't refering any date it gives the last date of calendar table so december 18 and gives blanks so if you want to have always refering to current date you need to add a filter in the visual until current date or change the.measures to something like this:

Sales MTD = CALCULATE(TOTALMTD([Sales Sum];'Date'[Date]); Date[Date] <= today())

Sales MTD PM = CALCULATE(TOTALMTD([Sales Sum];DATEADD('Date'[Date];-1;MONTH)); Date[Date] <= Today())

This measure not in pbix file.

Here is your pbix file with the changes (wetransfer download only available for 7 days)

Regards,

MFelix