Using "Year To Date"

I need help figuring out the best way for users to query "Year to Date" sales while giving them the option to exclude or include the current month.  

If a user types in "sales year to date" I expect that to be through today,  but what keywords do I use to create "sales year to date before this month" without typing in specific "before" date, "last 10 months", etc...   

6replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
  • Sales This Year will return everything up to the start of the current month.

    Sales This Year This Month will also include the current month

  • I get the same $$$ for both.   I think I'll need to create custom formula's to try and achieve this but was hoping it could easier be done with keywords like "before this month".  Thanks!


  • Try typing "today" at the end 

    It seems to work for me 

  • Greg Brown what is the date range selected? This should be visible as a summary in the table view. 

  • Typing "sales this year this month today" seems to produce an "OR" clause where it's returning a sum of sales contained within "this year" or "this month" or "today" which is basically the overlapping of dates 1/1/2019 through today. 

    What I'm looking for is a keyword way of saying "sales this year NOT this month" or "sales this year before this month".  A user wants to put it on a pinboard that automatically rolls with the calendar as each month passes and it would always shows total sales YTD as of last completed month.   If there's no keyword way to achieve that,  then I think I'll need to create a month table dimension that I join to containing "Last Date of Month" and then filter on that before today()

  • OK,  was able to work around this by adding separate "Month Begin Date" and "Month End Date" date fields to the worksheet.   Then the user can choose to function "Year to Date" on either field to include/exclude the current month.  

Like Follow
  • Status Answered
  • 6 mths agoLast active
  • 6Replies
  • 87Views
  • 5 Following