Forum Discussion

CraigBlackman's avatar
CraigBlackman
Helper 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
Helper 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
    Memorable 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.

    • CraigBlackman's avatar
      CraigBlackman
      Helper III
      10 years ago

      konstantinos

       

      After a bit of trial and error, the SUMX wrap has done the job, so many thanks for your help. Greatly appreciated.

       

      Craig Blackman

      • sksmith's avatar
        sksmith
        Regular Visitor
        10 years ago

        Hi all, new to the forum and new to DAX as well.  It seems like many people have this same question, yet I haven't yet been able to find an answer that works in my situation.

         

        What I want to do.  Compare sales year to date for current year (for example 1/1/2015 to 11/2/2015) to sales year to date previous year (for example 1/1/2014 to 11/2/2014)

         

        Here's my two formulas so far   

        Sales YTD:=TOTALYTD(sum(Sold[ExtPrice]),Sold[OrderDate])

         

        Sales Prev YTD:=CALCULATE(SUM(Sold[ExtPrice]),DATESBETWEEN(Calender[DateKey],date(2014,1,1),LASTDATE(DATEADD(Sold[OrderDate],-365,DAY))))

         

        My formula works fine in PowerPivot View, but I can't get around the dreaded contiguous date error whenever I create a pivot table.  I've tried messing with various IF(HASONEVALUE.... and SUMX... but haven't gotten a solution.  Again, I'm pretty new to all this so my understanding of some of these functions is pretty limited.  Any help would be greatly appreciated.

         

    • dmoysidis's avatar
      dmoysidis
      Frequent Visitor
      10 years ago

      Thanks Kostas. That helped me too!