Forum Discussion

steph_io's avatar
steph_io
Icon for Advocate II rankAdvocate II
9 years ago
Solved

direct query previous month

  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 co...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

    Hi steph_io,

     

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

     

    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

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi steph_io,

 

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

 

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's avatar
    steph_io
    Icon for Advocate II rankAdvocate II
    9 years ago

    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!