I have an NPrinting report that uses the attached chart to produce the current month data (sum(ALLOC_HRS) vs sum(ACT_HOURS). These reports run every Tuesday. The current NPrinting logic looks at =month(today()) and =year(today()) to dynamically pull the current data.
I have a new requirement that states "Report will deliver a cumulative and complete snapshot of the month actual v forecast for the individual project i.e. the report will show for the previous week(s) the cumulative actual hours up to the completion of the month. If the report is run in the first week of June it will provide the actual v forecast for the month of May in its entirety it will not start reporting on that new month"
What this means to me is the relationship between the run date and the month end of what is being reported. The idea is to get a complete snapshot of the previous months’ data… so the first report run in the new month should always be the snapshot of the previous month’s data and NOT include any data from the first week of the new month.
For example, the first report run in the month of July will be on Tuesday, 7/7. This report needs to only contain data from the month of June and NOT contain data from July. To make is more complicated, our Fiscal year runs July-June. Right now we are in FY 2015 but in July we will be in FY 2016.
I think this piece is correct:
Old filter formula: =month(today())
New :FISCAL_MONTH_NUM – if within first week of the new month, use previous month’s data
If((day(today())<8), month(today())-1, month(today()))
This one needs to be combined and I don't know how:
Old filter formula: =year(today())
FISCAL_YEAR_NUM – if within first week of the new month and the month is Jan-Jun, use current year else add one to the year to get the correct fiscal year
FISCAL_YEAR_NUM – if within July and not within the first week of the month, add one year to get the correct fiscal year else use current year
=If((day(today())<8) and month(today())<=6, year(today()) ,
If((Month(today())=7 and day(today()) > 7), year(today())+1