A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN'

[Meera556]

Hi,

I'm trying to create a new table for cretaing custom date range slicer as below

Date_filter = UNION(

                ADDCOLUMNS(DATESBETWEEN(table[date_column],TODAY()-30,today()),"Type","Default"),

                ADDCOLUMNS(CALENDAR(MIN(table[date_column]),MAX(table[date_column])),"Type","Custom"))

But while creating I'm getting 

A date column containing duplicate dates was specified in the call to function 'DATESBETWEEN' error

Can anyone help me with this

Thanks for your help in advance

[Alican_C]

DATESBETWEEN function generates a table of dates without ensuring uniqueness, which can result in duplicate dates when combined with other date tables.

Try:

Date_filter =
VAR DefaultDates =
SELECTCOLUMNS(
CALENDAR(TODAY() - 30, TODAY()),
"Date", [Date],
"Type", "Default"
)

VAR CustomDates =
SELECTCOLUMNS(
CALENDAR(MIN('table'[date_column]), MAX('table'[date_column])),
"Date", [Date],
"Type", "Custom"
)

RETURN
DISTINCT(
UNION(
DefaultDates,
CustomDates
)
)

[bhanu_gautam]

@Meera556 , Try this DAX measure

Date_filter =
UNION(
ADDCOLUMNS(
DATESBETWEEN(
DISTINCT(table[date_column]),
TODAY() - 30,
TODAY()
),
"Type", "Default"
),
ADDCOLUMNS(
CALENDAR(
MINX(DISTINCT(table[date_column]), [date_column]),
MAXX(DISTINCT(table[date_column]), [date_column])
),
"Type", "Custom"
)
)

[Meera556]

Hi @bhanu_gautam ,

I have tried using DISTINCT function then I'm getting the below error

DatesBetween and DatesInPeriod functions are only accepting date column reference as a first argument.

[bhanu_gautam]

You can create both steps separetly

Step 1: Create a table with unique dates

UniqueDates = DISTINCT(table[date_column])

Step 2: Create the Date_filter table using the unique dates

Date_filter = UNION(

    ADDCOLUMNS(DATESBETWEEN(UniqueDates[date_column], TODAY() - 30, TODAY()), "Type", "Default"),

    ADDCOLUMNS(CALENDAR(MIN(UniqueDates[date_column]), MAX(UniqueDates[date_column])), "Type", "Custom")

)