locked
'DATEADD' and Contiguous date selections RRS feed

  • Question

  • Hi,

    I have a measure for prior year sales called [Sales PY] sales that I use in YOY% calculations which uses DATEADD. Because we use a custom retail calendar I have to look for sales 364 previous as opposed to using -1,YEAR.

    =[Sales](DATEADD('01 - Calendar'[Date],-364,DAY))
    The problem I have is with the insistence of the function on receiving a contiguous set of dates - although this in itself is a pain, in many cases when I filter the calendar by multiple dimensions I get the error despite the fact that I am clearly delivering the contiguous dates.

    In the example below on the left I have filtered by [Sales PY] by Week 22 (F13-W22) and it works but if I add Week 21 as on the right it errors.

    If anybody has any suggestions I'd been keen to hear them.

    Thanks

    Jacob


    • Edited by barnettjacob Wednesday, December 5, 2012 3:06 AM
    Wednesday, December 5, 2012 3:06 AM

Answers

  • you can try this which works with non-contiguous selections:
    (the example is on AdventureWorks, but can be easly adopted)

    SA PY:=SUMX(VALUES(DimDate[FullDateAlternateKey]), CALCULATE([SA], DATEADD(DimDate[FullDateAlternateKey], -364, DAY)))

    what it does it takes all selected Dates in the current context using VALUES() and iterates over that table, calculates the values of the previous year for each day and sums this values up

    this link may also help to get some more insights on time-intelligence functions:
    http://mdxdax.blogspot.co.at/2011/01/dax-time-intelligence-functions.html

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, December 13, 2012 9:36 AM
    Answerer

All replies

  • Hi Jacob -

    One option is wrapping LASTDATE() around your date reference like below.  That will allow the query to resolve to a single date for each evaluation / cell. 

    =[Sales](DATEADD(LASTDATE('01 - Calendar'[Date]),-364,DAY))
    Let me know if that helps.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Wednesday, December 5, 2012 12:36 PM
    Answerer
  • Had another thought on this one.  Looks like the goal is to roll back 1 year for whatever period you're focusing on.  You can build that window of time into your filter in the calculation using DATESBETWEEN and manually passing in the rolled-back dates.  Something like:

    =CALCULATE([Sales]
               ,DATESBETWEEN('Date'[Date]
                             ,DATEADD(FIRSTDATE('date'[date]),-364,DAY)
                             ,DATEADD(LASTDATE('date'[date]),-364,DAY)
                            )
              )
    Let me know if that helps or if there are more details to the specific requirement.

    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Wednesday, December 5, 2012 1:27 PM
    Answerer
  • Brent, I really appreciate the suggestion!

    Fundamentally your measure works although I have two issues with it:

     - Its 20% slower than mine and this 'pattern' is used in over 20 measures (it drives all the YOY stuff and as a retailer its a big deal to us).

     - If somebody were to pick non contiguous dates then it would actually give a wrong answer which is obviously far worse than no answer at all! I guess I could package it with a test to prevent this from happening but that's not going to do anything for performance.

    Suspect I will just leave as is, the simple elegance of the solution just about outweighs this annoying 'bug'.

    Thank again.

    Jacob

    Thursday, December 6, 2012 5:27 AM
  • you can try this which works with non-contiguous selections:
    (the example is on AdventureWorks, but can be easly adopted)

    SA PY:=SUMX(VALUES(DimDate[FullDateAlternateKey]), CALCULATE([SA], DATEADD(DimDate[FullDateAlternateKey], -364, DAY)))

    what it does it takes all selected Dates in the current context using VALUES() and iterates over that table, calculates the values of the previous year for each day and sums this values up

    this link may also help to get some more insights on time-intelligence functions:
    http://mdxdax.blogspot.co.at/2011/01/dax-time-intelligence-functions.html

    hth,
    gerhard


    - www.pmOne.com -

    Thursday, December 13, 2012 9:36 AM
    Answerer