1 Reply Latest reply on Jul 30, 2013 1:38 PM by Mark Holtz

    Relative Date - YTD and MTD less one day

    Jerry Ward

      So we are trying to get a comparison of 2012 versus 2013 YTD and MTD.  Our data does not have results for the day of it only has data up until yesterday.


      I can get the correct results if I use the relative date filter and anchor to yesterdays date for both MTD and YTD.


      However I need to build a filter that is dynamic because I want the results to be auto emailed to me.


      I have built out two filters to filter out 2012 and 2013.


      This filter works for MTD for 2012 data:


      IF YEAR([StatDate]) = 2012

      AND MONTH([StatDate]) <= Month(TODAY())

      AND DAY([StatDate]) <= Day(dateadd('day',-1,Today()))

      THEN [Room Revenue] ELSE NULL END


      If I use this filter for 2012: IF YEAR([StatDate]) = 2012 THEN  [Room Revenue] ELSE NULL END...... I get data from 2012 but it will give me data up until todays date.  However I dont have 2013 data up untils todays date so it gives off an incorrect percent change.  I dont know why it still gives up until todays date even though I have the Relative Date filter in place below subtracting a day from the StatDate. 


      For 2013 I have this Calculated field this I use a relative date filter:


      2013:  IF YEAR([StatDate]) = 2013 THEN [Room Revenue] ELSE NULL END


      Relative Date Filter:  dateadd('year',datediff('year',[StatDate], dateadd('day',-1,TODAY())),[StatDate])


      What is the best way to build a filter for 2012 and 2013 so that it always takes todays date, less one day and give s the results for YTD and MTD???