Forum Discussion

webward3's avatar
webward3
New Member
9 years ago

Previous Month Measure

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 measur...
General Comment
Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
9 years ago
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's avatar
    webward3
    New Member
    9 years ago
    Eric_ZhangI have blank value for my measure when taking that approach.
    • Eric_Zhang's avatar
      Eric_Zhang
      Microsoft Employee
      9 years ago
      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.