Forum Discussion

CraigBlackman's avatar
CraigBlackman
Icon for Helper III rankHelper III
10 years ago
Solved

Function 'DATEADD' only works with contiguous date selections.

Hi All,   I have this error which is a measure calculated from net sales DATEADD -1 year.   MdxScript(Model) (1, 134) Calculation error in measure 'DimDate'[P/Y Sales1]: Function 'DATEADD' only w...
Need Help
Tutorial Requests
  • konstantinos's avatar
    konstantinos
    10 years ago

    CraigBlackmanYou can try  P/Y Sales = CALCULATE ( [Net Sales]; SAMEPERIODLASTYEAR( Dates[Date]))

    or wrap your P/Y Sales with SUMX 

    P/Y Sales = SUMX ( salestable; calculate( [Net Sales]; DATEADD( Dates[Date]; -1 ; Year )) 

     

  • CraigBlackman's avatar
    CraigBlackman
    Icon for Helper III rankHelper III
    10 years ago

    Hi,

     

    Thanks for that. The issue is that I want to display a total for these columns.

     

    I have this table, the P/Y Sales and P/Y Orders a\re the problem columns here, both based on the current date -1 Year.

     

    Maybe I can calacultae this in another fashion?

     

    Capture.PNG

    • konstantinos's avatar
      konstantinos
      Icon for Memorable Member rankMemorable Member
      10 years ago

      CraigBlackmanYou can try  P/Y Sales = CALCULATE ( [Net Sales]; SAMEPERIODLASTYEAR( Dates[Date]))

      or wrap your P/Y Sales with SUMX 

      P/Y Sales = SUMX ( salestable; calculate( [Net Sales]; DATEADD( Dates[Date]; -1 ; Year )) 

       

      • Ronald's avatar
        Ronald
        Regular Visitor
        10 years ago

        Hi konstantinos, I'm a new guy here in the community and a newbie using DAX and Power BI. I had the same function error but it turns me to this approach by getting to resolved the same issues. Having alike, the documentation can be found here => https://msdn.microsoft.com/en-us/library/ee634763.aspx are definetly could help a lot.

        The scenario is that this table has an incomplete DateMonth in one particular year in the middle of a FiscalYear ranges from 2001-2016. By having a slice n' dice using this filter to my Fact table then one of the year has incontiguous records by then the error shows. 

        The DAX script below shows to me on how to get rid off the issue to an incontiguous list of dates in my CalendarFiscal table.

        [Measure] = TOTALYTD(CALCULATE(SUM(FactTable[Amount]), DATEADD(Calendar[Date], -1, YEAR)), Calendar[Date], ALL(Calendar), "6/30")

        Yep, it works to me.