One of my customers has reports scheduled to run every weekday. The rule is that all month long it should pull the dates for the current month. But once a month, at the beginning of the month, they want to run the entire previous month. So I wrote their selection formula to say “If today is the first of the month, then run LastFullMonth, otherwise run MonthToDate:
If Day (currentDate) = 1
then {DateField} in LastFullMonth
else {DateField} in MonthToDate
This worked fine for several months until we got to April. Because the report only runs on weekdays, and because April first was a Sunday, the March report didn’t get run. Nothing ran on Sunday, and the report that ran on Monday April 2 was for April. So we needed to tweak the logic and calculate the first weekday of the month. So I wrote a formula called “FirstWeekDay” that looked like this:
If DayOfWeek (Minimum (MonthToDate)) = 7 then 3 else
If DayOfWeek (Minimum (MonthToDate)) = 1 then 2 else 1
It returns a number from 1 to 3 which represents the first weekday of the current month. Then I replaced the ‘1’ in the selection formula with my new formula field, like this:
If Day (currentDate) = {@FirstWeekDay}
then {DateField} in LastFullMonth
else {DateField} in MonthToDate