Get current year to date until last full month

[valpassos]

Hi community!

I have a CurrentYearToDate variable in my script that goes like this:

LET vSetCurrentYearToDate = 'Calendar_YearMonth_Seq = {">=' & Chr(36) & '(=Min({1<Calendar_Year = {"' & Chr(36) & '(=RangeMin(Year(Today()),Max(Calendar_Year)))"}>} Calendar_YearMonth_Seq))<=' & Chr(36) & '(=RangeMin(Max(Calendar_YearMonth_Seq),Max({1<Calendar_Date={"<=' & Chr(36) & '(=Today())"}>} Calendar_YearMonth_Seq)))"},' & Chr(10) &
'Calendar_Date = {"<=' & Chr(36) & '(=Date(RangeMin(Max(Calendar_Date),Max({1<Calendar_Date={"<=' & Chr(36) & '(=Today())"}>} Calendar_Date))))"},' & Chr(10) &
'Calendar_Year = ,' & Chr(10) &
'Calendar_MonthDesc = ,' & Chr(10) &
'Calendar_Month = ,' & Chr(10) &
'Calendar_YearMonthDescShort =';

But now I want to tweak this formula so it gives me all dates of this year until last complete month. We are in November, so I want dates from January 2018 to October 2018. How can I accomplish this in the variable?

Thanks!

Lisa

[jlongoria]

I'm not sure how you're using that variable. But you can define a variable to get the last day of the previous month like this:

=date(MonthStart(today())-1) // Calculate last day of previous month

From there you can use Month(), Year(), etc to get what you need for previous complete month. The only catch is if you want the previous month when Today() is the last day of the current month. 🙂