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???
Could you create a calculated field that adds a day to every date value you have--DATEADD('day',1,[DateField])--then use that in the relative date filter, but still display the original date field?
If you need more help, it'd be best if you could post a packaged (.twbx) workbook. So Your Question Didn't Get Answered...