Previous month to date DAX syntax

[lochem]

Hi all,

I am trying to build a formula to compare MonthToDate (MTD) and Last month todate (LMTD) values. MTD is easy enough, however its the last month's calculation that i cannot get.
If today is 14-March, and my KPI ("myExpression") for the month to-date is currently at, say 250, I want to compare this value with the same measurement that is calculated for the same date range of February, from 1-Feb to 14-Feb.

However i am running into errors with my logic...

I am looking for something similar to below:

LastMTD:=CALCULATE([myExpression], DATESBETWEEN([Date_Column],start_date<start_date>,end_date<last_date>))

for start_Date <start_date>and end_date <end_date>i am using the following:

FirstDateLM:=FIRSTDATE(dateadd(datesmtd(OpsCalendar[Date]),-1,month))

and

LastDateLM:=LASTDATE(DATEADD(DATESMTD(OpsCalendar[Date]),-1,MONTH))

two undesirable things are happening:
1. in my calendar, the date column ends at 31 dec 2016. my firstdateLM and lastdateLM fields are returning Nov 1 and Nov 30 2016, instead of actual last month first day and actual 30 days ago, etc.

2. i am getting an error in my LastMTD formula as follows: "A Table of multiple values was supplied where a single value was expected"
im guessing this has something to do with first and last dates in the DATESBETWEEN function, but i cannot be certain.

Please help?</end_date></start_date></last_date></start_date>

 

[lochem]

Clarifying, the concern would be "the fact table had nothing for Feb 12". Or *any* other day. time intelligence functions in dax must have a row for EVERY day... which is why there are typically built off a separate calendar table.

interesting....in that case, there are definitely days that do not appear at all in the fact table.

however, i checked the returned value from the formula using the 'local' date column and it actually returns the correct value, no problems.
it was only when i referenced the calendar table 'date' that returned a blank value.