Period / Year selection criteria for Automation

[Jillp]

I am trying to automate a report that I have previously been running manually. When running manually, I am able to enter the Year and the period of the data that I would like to select. I have come up with the selection criteria below that seems to work properly. My concern is in January; what formula could I add that would search to the previous year, and the previous period? This is probably a simple solution, that I am overthinking. .... I appreciate any advice! --Jill

Selection Criteria:
{APInvHed.Company} = "MSMO" and
ToText(dateadd('m',-1,currentdate),'M/yyyy')={@Month_Year}

@Month_Year:
totext({APInvHed.FiscalPeriod},00) & "/" & totext(ToNumber({APInvHed.FiscalYear}),"#")

 

[lbass]

Dateadd() will calculate correctly and return the previous year—if your fiscal year matches the calendar year. If it doesn’t, please explain the date range for your fiscal year and whether it is named for the beginning or end of that year.

I think you need to change your totext() condition to ‘MM/yyyy’ to match the {@Month_Year} output, though.

-LB

 

[Jillp]

Hi lbass,

Thanks for your response.

Our periods are run on a 4-4-5 calendar that are identified in our database. If I understand you correctly, the dateadd('m',-1,currentdate will get me back to the previous month, regardless if it is in a different year - therefore, it is not necessary to adjust the year. Is this correct?

Thanks again,

Jill

 

[lbass]

Dateadd() used on a January 2020 date would return Dec 2019, but if you are trying to match a fiscal year that differs from the calendar year, you will need to adjust the formula. For example, if your fiscal year runs July to June, and the fiscal year uses the end year as a name, then you would want the result NOT to revert to the actual year of the December date, and you would have to write a conditional formula. I don’t know exactly what your periods would look like, but the conditional part of the formula would look something like this, assuming a July to June FY:

(
(
month(currentdate) in [2 to 7] and
Totext(dateadd(‘m’,-1, currentdate),’MM/yyyy’)={@Month_Year}
) or
(
Month(currentdate) in [1, 8 to 12] and
Totext(dateadd(‘yyyy’,+1, dateadd(‘m’,-1,currentdate)),’MM/yyyy’)={@Month_Year}
)
)

Please recreate this formula or at least replace all apostrophes so that the formula does not fail.

-LB