locked
Date formuls from Collie's powerpivot blog RRS feed

  • Question

  • Hello. I'm working on some date stuff from this site: http://www.powerpivotpro.com/2011/10/slicers-for-selecting-last-x-periods/#comment-274090

    Everything is working well in my recreation but then I get this error:MdxScript(Model) (6,60) Calculation error in measure ‘DateTable[Slected_X_Startmonth]: Function ‘Dateadd’ only works with contigous date selections. What did I do wrong?

    How do I fix this? 


    Tuesday, September 6, 2016 7:04 PM

Answers

  • Hi Albo44,

    Do you get the error when you run the following measure?

    Selected_X_Months_Start_Date = LASTDATE(DATEADD(Year_Period[Next_Month_Start_Date],[Selected_X]*-1,MONTH))

    I am trying to create “Selected_X_Months_Start_Date” in my local environment, however, I can get the expected result without any issues. So the formula syntax is right.

    After my research, the error is caused by two reasons:

    1. Date column used in DATEADD function must have a contiguous range of dates. This error can be returned if there is an incontiguous date value in one or more rows in the date column. Please double check your date column’s values to make sure they are in a contiguous order.

    2. This error can be displayed when a calculated field with a Time Intelligence Functions (DAX) is placed in the VALUES area of a PivotTable and date fields such as month or quarter are selected as slicers or filters before selecting a year. In your scenario, first add time in function, then add X, Period and Year as a slicer or filter.

    For more details, you can review the examples which help you better understand in this article.

    Regards,
    Angelia


    Wednesday, September 7, 2016 10:18 AM