Issue with using DATEADD

[Anonymous]

Hello everyone,

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. 

Number of Sales YTD = CALCULATE(
                                                DISTINCTCOUNT('Sales'[Sales ID]),
                                                DATESYTD('Sales'[Sales Date]))

Number of Sales YTD Comparison = CALCULATE(

                                                                  [Number of Sales YTD],

                                                                   DATEADD(

                                                                                'Sales'[Sales Date],

                                                                                -(YEAR(TODAY())-'Selected Year'[Parameter Value]),

                                                                                 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. 

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. 

Any tips would be appreciated 🙂

[VahidDM · Accepted solution]

HI   @Anonymous

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] >= DATE( _SD, 1, 1 )
                && 'Sales'[Sales Date] <= _TSD
        )
    )

It'd would be great to add a date table to your model and use that, for adding a date table please check this link: https://www.vahiddm.com/post/creating-calendar-table-with-3-steps

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[wdx223_Daniel]

Time Intelligence Functions need a real date table with full dates.