direct query previous month

[steph_io]

With Direct Query, some built in functions are no longer working.

I need a measure for previous month, previous quarter, previous year... to calcualte variance and growth. These measure should consider the previous month, year that falls outside of the slicer filter as well. 

Here are my formulas for Current Month CM and Priort Month PM:

Trans Accounts CM = CALCULATE([Trans Accounts],FILTER(ALL(DimDate), DimDate[FirstDayOfMonth] = max(DimDate[FirstDayOfMonth])))
Trans Accounts PM = CALCULATE([Trans Accounts CM],DATEADD(DimDate[FirstDayOfMonth],-1, MONTH))

How do I change the PM formula to consider the previous month that falls before the date slicer?

[v-ljerr-msft · Accepted solution]

Hi @steph_io,

If I understand you correctly, you should be able to follow steps below to get your expected result. 

1. Use the formula below to add new calculate column in your DimDate table.

YearMonth =
YEAR ( DimDate[FirstDayOfMonth] ) * 12
    + MONTH ( DimDate[FirstDayOfMonth] )

2. Then you should be able use the formula below to calculate the Trans Accounts PM.

Trans Accounts PM =
CALCULATE (
    [Trans Accounts],
    FILTER ( ALL ( DimDate ), DimDate[YearMonth] = MAX ( DimDate[YearMonth] - 1 ) )
)

Regards

[steph_io]

This also resolves the issue I had with my calculations not looking at prior month when it fell in prior year. My formula wasn't working for each january. Thank you!