Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DATEADD not working on certain date values

I have a calculated column in my data set (DataTable2019 Test) which is looking at a date column (History Date) subtracting one year from the date and then returning a custom formatted date. For full context here I'm trying to show the Jan 1st dates as the year end (YE) count for that given year.

 

This is the formula below:

Datetable2019 Test = FORMAT(DATEADD('Employee History'[History Date], -1, YEAR), """YE"" yyyy")
 
The formula works perfectly for the 2021, 2022 and 2023 dates in the History Date column, but for the 2020 dates I am getting back blank.
 
Separately, I seem to have lost the ability for a matrix using this column to be sorted by date. Instead it's being sorted alphabetically.
 
Please help!
  • AUaero's avatar
    AUaero
    4 years ago

    I'm guessing your date table doesn't have dates prior to 2020.  DATEADD doesn't work by adding or subtracting from a given date, it returns a result from your date table shifted by the interval you've specified.  If no dates exist prior to 2020, you won't get a result.  Take a look at the DATEADD documentation for more info.
    DATEADD function (DAX) - DAX | Microsoft Docs

5 Replies

  • NehaSingh_108's avatar
    NehaSingh_108
    New Member
    2 years ago

    Hey,

    Try adding .[Date] to your date column name while using it to create a DAX. 
    For example, 

    Created Date (Corrected) = IF(YEAR('Raw Dump'[Created Date])="2022", DATEADD('Raw Dump'[Created Date].[Date],-1,year),'Raw Dump'[Created Date])
  • AUaero's avatar
    AUaero
    Responsive Resident
    4 years ago

    I'm guessing your date table doesn't have dates prior to 2020.  DATEADD doesn't work by adding or subtracting from a given date, it returns a result from your date table shifted by the interval you've specified.  If no dates exist prior to 2020, you won't get a result.  Take a look at the DATEADD documentation for more info.
    DATEADD function (DAX) - DAX | Microsoft Docs

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      That makes sense, thank you.

       

      is there another function that can just subtract 1 year from the date in the history table? 

      so for the 2020 dates in my table, they would return a result of "YE 2019".

       

      • AUaero's avatar
        AUaero
        Responsive Resident
        4 years ago

        Take a look at the EDATE function

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    I had the same issue with DATEADD and adding multiple years to a certain column. EDATE works perfect in this case EDATE(<date-field, ,nr of months>)