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

     

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.