Calculation groups - calendar issue

[Anonymous]

Hello

I am using calculation groups to compare a series of current measures to the previous quarter and previous December.

For some reason the previous December is not working. The DAX i am using is as follows:

VAR _Date = MAXX ( ALLSELECTED ( Sales ), Sales[QUARTER_END_DATE] ) 
VAR _LastDec = DATE ( YEAR ( _Date ) - 1, MONTH ( _Date ), DAY ( _Date) ) 
VAR _RESULT = CALCULATE ( SELECTEDMEASURE (), FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] = _LastDec ) ) 

RETURN 
_RESULT

Using this DAX the data returned is the same as the original measure, i.e. selectedmeasure (). 

Any comments on this DAX or suggestions on different ways to filter by previous December are most welcome. 

I use a custom column within a calendar table to create a relative month, as follows:

RelativeMonth = [Year] * 12 + 'Calendar'[MonthNumber]

- (MAX([Year]) * 12 + MONTH(MAX('Calendar'[Date])))

Which is great for calculating previous quarter or previous month, so if anybody knows of something similar for calculating a specific month, like previous December. 

Many thanks in advance for any help

[Anonymous]

Hi @Anonymous ,

You could consider to use date column as a slicer.

Then create a measure like below:

current = calculate([measure],filter(all(calendar),date = selectedvalue(calendar[date])))

previous = calculate([measure],filter(all(calendar),date = edate(selectedvalue(calendar[date]),-12)))

Best Regards,

Jay

[Anonymous]

Thanks for your response. I like EDATE function, which is a good alternative to using the relative month DAX that I use. However, I can't see how your DAX for 'previous' will allow me to produce a data point for the previous December. 

The issue I am having is I need to always bring through the previous December figure, regardless of the month I am in. So using 

date = edate(selectedvalue(calendar[date]),-12

I don't think will give me a figure for the previous December. Correct me if I am wrong.

It's really just this bit of the DAX that I need suggestions on alternatives to creating a date filter. 

VAR _LastDec = DATE ( YEAR ( _Date ) - 1, MONTH ( _Date ), DAY ( _Date) ) 

 I think the issue with this is that I am using another date to populate the month and Day variables, which in June will mean the MAX day is 30, rather than 31 needed for December, as the last day of the month. 

[amitchandak]

@Anonymous , do you need something like

last year last month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1*month(selectedvalue(calendar[date])),MONTH)))

last year last month = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1*month(Today()),MONTH)))