Forum Discussion

Anonymous's avatar
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 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

3 Replies

  • Anonymous's avatar
    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's avatar
      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's avatar
        amitchandak
        Icon for Super User rankSuper 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)))