Forum Discussion

Anand_ch's avatar
Anand_ch
New Member
3 years ago
Solved

How To work with DAX to remove duplicate dates

My Quey :   Forum_Event = (SWITCH(TRUE(),'ALL Members'[Date Forum Joined]<=DATEADD('ALL Members'[Date Joined],90,DAY),'ALL Members'[External User ID])/'ALL Members'[External User ID])
 
 
 
Error:A date column containing duplicate dates was specified in the call to function 'DATEADD'. This is not supported.
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi, Anand_ch 

    According to your description , Wen you used DATEADD() function , It returned you an error. Right ?

    First of all, the DATEADD() function is a time intelligence function, and when we use it, it is best to ensure that the date column is a continuous and non-repeating column.

    Here are the steps you can follow:

    (1)We can click "New Table" and enter :

    CALENDAR= CALENDAR(FIRSTDATE('ALL Members'[Date Joined]),LASTDATE('ALL Members'[Date Joined]) )
    (2)Then we need to establish a one-to-many relationship between the table 'ALL Members' and the table 'CALENDAR' based on [Date].
    (3) When we use the date column, we can replace it with the 'CALENDAR'[Date] we created.If it's a visual, we also need to replace our 'CALENDAR'[Date] field in the row header.
     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi, Anand_ch 

    According to your description , Wen you used DATEADD() function , It returned you an error. Right ?

    First of all, the DATEADD() function is a time intelligence function, and when we use it, it is best to ensure that the date column is a continuous and non-repeating column.

    Here are the steps you can follow:

    (1)We can click "New Table" and enter :

    CALENDAR= CALENDAR(FIRSTDATE('ALL Members'[Date Joined]),LASTDATE('ALL Members'[Date Joined]) )
    (2)Then we need to establish a one-to-many relationship between the table 'ALL Members' and the table 'CALENDAR' based on [Date].
    (3) When we use the date column, we can replace it with the 'CALENDAR'[Date] we created.If it's a visual, we also need to replace our 'CALENDAR'[Date] field in the row header.
     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly