Forum Discussion

SteveG_91's avatar
SteveG_91
Icon for Helper I rankHelper I
4 years ago
Solved

DATEADD and EDATE

Greetings.  I have a table named "Refresh Date" that updates with the date/timestamp that my report was last refreshed.  There is only one column, named "RefreshDate" and one row in the table.  I need to show the same same date/timestamp but for the previous year.  For instance, if the RefreshDate is 6/13/2022 8:37:45 AM I need to also display 6/13/2021 8:37:45 AM.  I tried a measure using dateadd as follows

dateadd('Refresh Date'[RefreshDate],-1,YEAR)

but that returns a blank value
 
I also tried using EDATE, as follows
edate('Refresh Date'[RefreshDate],-12)
That correctly subtracts one year but it changes the timestamp to 12:00:00 AM (it returns 6/13/2021 12:00:00 AM)
 
How do I preserve the timestamp while also subtracting one year?
 
Thanks in advance!
Steve
Need Help
  • HotChilli's avatar
    HotChilli
    4 years ago

    You could use the EDATE formula you have and then add the time portion of the original field i.e. 

    + TIMEVALUE([Column1]) 
     
    I will admit that I didn't expect it to work but it did.

2 Replies

  • HotChilli's avatar
    HotChilli
    Icon for Community Champion rankCommunity Champion
    4 years ago

    You could use the EDATE formula you have and then add the time portion of the original field i.e. 

    + TIMEVALUE([Column1]) 
     
    I will admit that I didn't expect it to work but it did.
  • SteveG_91's avatar
    SteveG_91
    Icon for Helper I rankHelper I
    4 years ago

    Brilliant HotChilli, that worked perfectly.  I did try something similar (edate('Refresh Date'[RefreshDate],-12) & " " & TIMEVALUE('Refresh Date'[RefreshDate])) but the result was formatted as text.  You solution of combining with a "+" kept it formatted as date/time.  Many thanks!