I have multiple years of data in my table.
I would like to look at prior year to date (e.g., 1/1/16 - 2/3/16)
I'm using:
//Calc_YTD Prior
if [Date] <= TODAY() and datediff('year',[Date], TODAY())=1
then ([Records])
end
but I'm returning counts for all the dates in 2016, not just YTD...
?
thank you.
Mark,
[Date] <= TODAY() will yield all records prior to today (February 3, 2017). So it's really not contributing to your calculation.
Try this instead:
if datepart('dayofyear',[Date] ) <= datepart('dayofyear',TODAY() )
and datediff('year',[Date], TODAY()) = 1
then ([Records])
end
Or, if you need to it to stay in month/day sync even after a leap year day (February 29), try this trick. It will cause February 29 to get rolled in with March 1.
if month([Date])*100+day([Date]) < month(TODAY())*100+day(TODAY())
and datediff('year',[Date], TODAY()) = 1
then ([Records])
end
Hope this helps.
