How to create measures for the previous month and same month last year?

[Nsimpson]

When I try to create the below measure I get the following error.

TEST Prev Month = CALCULATE(SUM('tbl6PakData_Archive'[Inv6Pak]),DATEADD('tbl6PakData_Archive'[Month],-1,MONTH))

I would also like to show values for the same month 12 months in the past using a simliar measure.  

Here is what my month column looks like. The data type is Date.

Vendor Name	Last Month	Same Month LY
Test Vendor	$45,000 	$30,000 

Any suggestions?

[v-luwang-msft · Accepted solution]

Hi @Nsimpson ,

Please adjust your measure according to the following template：

premonth = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),DATEADD('Table'[date],1,MONTH)=  MAX('Table'[date])))

preYEAR = CALCULATE(MAX('Table'[value]),FILTER(ALL('Table'),DATEADD('Table'[date],1,YEAR)=  MAX('Table'[date])))

Did I answer your question? Mark my post as a solution!

Best Regards

Lucien

[YukiK]

DATEADD() uses a date table containing dates.

Do you have some other column like "relative month value" with values of 0,-1,-2, etc? If so, then you can do something like this:

TEST Prev Month = CALCULATE(SUM('tbl6PakData_Archive'[Inv6Pak]), "your table"[relative month value] = -1  )

[Nsimpson]

I added relative month to my date table but and updated the measure to the below and all the values were blank.  Any other suggestions?

TEST Prev Month = CALCULATE(SUM('tbl6PakData_Archive'[Inv6Pak]), 'M1VAULT_STG DATE_DIM'[Relative_Month] = -1)