Issue with using DATEADD

Question

Hello everyone,&nbsp;I have some issues with the DATEADD function when selecting certain filters on the dashboard for calculating the number of YTD sales for a selected year.&nbsp;&nbsp;Number of Sales YTD = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DISTINCTCOUNT('Sales'[Sales ID]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; DATESYTD('Sales'[Sales Date]))Number of Sales YTD Comparison = CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; [Number of Sales YTD],&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;DATEADD(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Sales'[Sales Date], &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; -(YEAR(TODAY())-'Selected Year'[Parameter Value]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;year))This works perfect for any selected year. However, when I filter down to a specific category by 'clicking' it on the page, I get an card error message that says "DATEADD expects a contiguous selection when the date column is not unique, has gaps or it contains time portion.&nbsp;I noticed when I replace DATEADD with PARALLELPERIOD, this seems to solve the problem (I can filter down to categories with no error with the card message) but instead of providing the YTD sales for the selected year, I get the full year sales for the selected year. I.e. if I had selected 2019, the [Number of Sales YTD Comparison] would show the number of sales for 2019, rather than the number of sales YTD for 2019.&nbsp;&nbsp;Any tips would be appreciated 🙂

vahiddm · Accepted Answer

HI&nbsp;&nbsp;&nbsp;Anonymous&nbsp;Number of Sales YTD Comparison =
VAR _SD = 'Selected Year'[Parameter Value]
VAR _T =
    TODAY()
VAR _TSD =
    DATE( _SD, MONTH( _T ), DAY( _T ) )
RETURN
    CALCULATE(
        DISTINCTCOUNT( 'Sales'[Sales ID] ),
        FILTER(
            ALL( 'Sales' ),
            'Sales'[Sales Date] &gt;= DATE( _SD, 1, 1 )
                &amp;&amp; 'Sales'[Sales Date] &lt;= _TSD
        )
    )&nbsp;It'd would be great to add a date table to your&nbsp;model and use that, for adding a date table please check this link:&nbsp;https://www.vahiddm.com/post/creating-calendar-table-with-3-steps&nbsp;Appreciate your Kudos!!LinkedIn:&nbsp;www.linkedin.com/in/vahid-dm/&nbsp;&nbsp;

Forum Discussion

Issue with using DATEADD

2 Replies

Related content

DATEADD issue using variables

DateAdd issue

DATEADD using EARLIER function

DATEADD issue

Facing issue with DATEADD function