Hello,
I think you should take into consideration WHEN you plan to run the report. If you're running the report at the close of the the period (current month -1) then your formula must reflect that.
One way is the include the entire period because there shouldn't be any data saved in advance (current date/time +).
Your MTD formula may look like this:
If Month(CurrentDateTime) >1 Then //Current Date of Feb and beyond
If Month({Date/Time Field}) = Month(currentdatetime)-1
and Year([Date/Time Field]) = Year(Currentdatetime) Then
{Your Value Field}
Else if Month(CurrentDateTime) = 1 Then
If Month({Date/Time Field}) = Month(currentdatetime)-1 //Or hardcode 12
and Year([Date/Time Field]) = Year(Currentdatetime) -1 Then
{Your Value Field}
Else
00.00;
Your YTD formula is much easier:
If Year([Date/Time Field}) = Year(CurrentDateTime) Then
{Your Value Field}
Else
0.00;
I hope this answers your question. Good luck!
Edited by MWToine - 13 Oct 2011 at 9:43am
IP Logged
