This is completely do-able with a few calculated fields using the datetrunc function. It's much easier and more efficient, I find, to use that rather than built in date calculations when you use a lot of different calculations like you have.
In the attached workbook, I created 5 new date fields, and 4 new calculations. (Note because I'm using the Coffee sample data and the dates only go through 12/2011, I created a fake variable to use instead of today, called "TodayFAKE" - in your situation you'd use Today())\
Date extraction based on current time:
- CurrentMonth: date(datetrunc("month",Today()))
- CurrentYear: date(datetrunc("year",Today()))
- LastMonth: date(datetrunc("month",(datetrunc("month",Today())-1)))
Date extraction based on the data:
- DateMonth: date(DateTrunc("month",[Date]))
- DateYear: date(DateTrunc("year",[Date]))
Then create the 4 fields you have shown in your sample:
- Sales-CurrentMonth: IF [DateMonth]=[CurrentMonth] THEN [Sales] ELSE 0 END
- Sales-CurrentYear: IF [DateYear]=[CurrentYear] THEN [Sales] THEN [Sales] ELSE 0 END
- Sales-LastMonth: IF [DateMonth]=[LastMonth] THEN [Sales] THEN [Sales] ELSE 0 END
- Sales-MonthDifferencePercent: (sum([Sales-CurrentMonth])-sum([Sales-LastMonth]))/sum([Sales-LastMonth])
And do the same with any other additional calculations you want to do.
DateSolutions.twbx.zip 362.7 KB