Display Prior Month

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered

trying to display invoices from the month prior to the current date. using calculated column. formula is (Today()) and Year([Invoice Date])=Year(Today()), "Prior Month","Not Prior Month")  I think i need to put a -1 in here somewhere to get the prior month but havntt been able to add it without a syntax error.

Photo of Chris

Chris

  • 60 Points

Posted 3 years ago

  • 0
  • 1
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
Ok - [Invoice Date] is a Date field.

Say you have [Prior Month?] as a Formula Text field - try using the following formula:


var Date PriorMonthDate = AdjustMonth(Today(), -1);

If ( Month([Invoice Date]) = Month($PriorMonthDate) and Year([Invoice Date]) = Year($PriorMonthDate), 

"Prior Month",

"Not Prior Month"

)

Use the AdjustMonth function (with the parameter -1) to find what the prior month would be from today.  We don't care about the day in this case - just the month and the year.

Then compare the Month and Year from [Invoice Date] - it needs to match that of PriorMonthDate - for it to be "Prior Month".  Otherwise it's "Not Prior Month".

Depending on your use case - you may want to change this to a Formula Checkbox field instead - because a checkbox field might be easier to use in filters, and won't be dependent on comparing the exact text "Prior Month" / "Not Prior Month", etc.
Photo of Chris

Chris

  • 60 Points
That worked! Thank you so much!
Photo of Xavier Fan

Xavier Fan, Champion

  • 410 Points 250 badge 2x thumb
Great - you're welcome!