Forum Discussion

Anonymous

Not applicable

4 years ago

Calculation groups - calendar issue

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 ...

Anonymous

Not applicable

4 years ago

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
Not applicable
4 years ago
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
  Super User
  4 years ago
  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)))