Previous Month Measure

[webward3]

I have a date table joined to my fact table. I'm trying to create a previous month measure in my tabular model. When I use the following formula referencing my date table, I get the exact same measure as the current month:

Measure 1:=CALCULATE([OTD to CRD], DATEADD(DimDate[Date], -1, month))

When I use the date filed in my fact table, I get the previous month value, however I'm not able to use the measure with any dimension or slicer. I get error "DATEADD only works with contiguous date selections:

Measure 1:=CALCULATE([OTD to CRD], DATEADD(Global[RequestDate], -1, month))

Also, when I try to create a MTD measure using the Date table, it returns a (blank) value. Works well when I use date field in my fact table

Measure 2:=CALCULATE([OTD to CRD], DATESMTD(DimDate[Date]))

What am I missing?

[Eric_Zhang]

---

@webward3 wrote:

I have a date table joined to my fact table. I'm trying to create a previous month measure in my tabular model. When I use the following formula referencing my date table, I get the exact same measure as the current month:

Measure 1:=CALCULATE([OTD to CRD], DATEADD(DimDate[Date], -1, month))

When I use the date filed in my fact table, I get the previous month value, however I'm not able to use the measure with any dimension or slicer. I get error "DATEADD only works with contiguous date selections:

Measure 1:=CALCULATE([OTD to CRD], DATEADD(Global[RequestDate], -1, month))

Also, when I try to create a MTD measure using the Date table, it returns a (blank) value. Works well when I use date field in my fact table

Measure 2:=CALCULATE([OTD to CRD], DATESMTD(DimDate[Date]))

What am I missing?

---

@webward3

Could you try 

Measure 1 = CALCULATE([OTD to CRD], PREVIOUSMONTH(dimdate[Date]))

[webward3]

@Eric_ZhangI have blank value for my measure when taking that approach.

[Eric_Zhang]

---

@webward3 wrote:
@Eric_ZhangI have blank value for my measure when taking that approach.

---

Could you share any sample data? Even better you can share the pbix file. You can upload it to any network drive and share the link. Do mask sensitive data before uploading.

[Sean]

Using your Measure 2

Create this Measure

Previous Month Measure =
CALCULATE ( [Measure 2], DATEADD ( DATESMTD ( DimDate[Date] ), -1, MONTH ) )

or this should also work

Previous Month Measure =
CALCULATE ( [Measure 2], PARALLELPERIOD ( DimDate[Date], -1, MONTH ) )

[Sean]

What is the range of your Calendar Table?

[webward3]

Thanks Sean but still getting same results.