Formula question

I built the formulas shown below and I am trying to change the "2016" to "last year" and the "2017" to "this year" so it will not need to be updated each year.  The reason I am using formulas for this is that I want to show each year as its own column in the report but I also have other formulas that show first sale date and last sale date, etc so I can't just pull in the sales by year and pivot...too much other info in the report.  Is there a way to replace 2016 with 'last year'...i currently get an error...

if(year ( date / time )=2016)then sales else 0 

if(year ( date / time )=2017)then sales else 0

4replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  •  Jason Beaulieu  you can use today() in a formula to achieve this. Here are some formulas to try:

    Last Year:

    if(year ( date/time) = year( add_days(today(),-365 ))) then sales else 0

    This year:

    if(year ( date/time ) = year( today() )) then sales else 0

    Reply Like 1
  • Vanessa McAfee Thank you!  One more question.  How would I get last year to show the same time frame as the YTD range for this year??

    Reply Like
  • To get Last YTD sales, the formula would be 

     

    if(year ( date/time ) = year( today()) and day_number_of_year(date/time)< day_number_of_year(today()) then sales else 0

    Reply Like
  • Siva Singaram Thanks Siva, I'll give it a try!

    Reply Like
login to reply
Like Follow
  • 11 days agoLast active
  • 4Replies
  • 114Views
  • 4 Following