SSRS Prior Month, MTD Same period as current month

0
votes

Should be easy right?

Today is Aug 11, 2014. I want a column that reflects MTD Which I have:

=Count(IIf(Year(Fields!Date.Value) = Year(Today) And Month(Fields!Date.Value) 
= Month(Today), Fields!ID.Value, Nothing), )

BUt I also want a column in SSRS that shows me the same time frame for the previous month, 7/11/2014.

I can get the date to appear, but I can't for the life of me figure out how to grab the data like I have for the current month

=FormatDateTime(DateSerial(DatePart("yyyy",today()), DatePart("m",today())-1, 
 DatePart("d",today())), DateFormat.ShortDate)
1
reporting-servicesssrs-2008ssrs-2008-r2
Not 100% sure what the question is. Are you referring to replacing "today()" function with your field? =FormatDateTime(DateSerial(DatePart("yyyy",Fields!Date.Value), DatePart("m",Fields!Date.Value)-1, DatePart("d",Fields!Date.Value)), DateFormat.ShortDate) – Tak
are you saying you want to compare a current MTD total vs. previous MTD total for same time frame. AKA "how was I doing at this time last month?" – lethaljd
@lethaljd, yes. I want to see how I'm doing this month compared to same time last month. – donviti

1 Answers

1
votes

the second column formula would look something like =Count(IIf(Year(Fields!Date.Value) = Year(Today().AddMonths(-1)) And Month(Fields!Date.Value) = Month(Today().AddMonths(-1)) AND DatePart("d", Fields!Date.Value) <= DatePart("d", Today()), Fields!ID.Value, Nothing), )