Trying to get Last years sales as a calculated field

[seancsn]

I have used the following formulas without success, with the error "Semantic Error: The value for Monthdate cannot be determined...."

Last Years Sales:=CALCULATE([Total Sales]),DATEADD([Monthdate],-1,Year))

Last Years Sales:=CALCULATE([Total Sales]),SAMEPERIODLASTYEAR([Monthdate])

The dates contained within Monthdate are of the format day/month/year. There are only 13 dates to represent the months data ie. 1/4/14, 1/5/14 etc but they are repeated a number of times for each reps sales.

Please could you advise what I am doing wrong?

Thanks in advance

Sean

 

[ImkeF]

Hi Sean,
Time intelligence functions need a contingeous date table to be connected to your Sales table.
In your formula you then actually should reference the date columnn from that date table and not from your sales table.
Best thing even to hide all your transaction tables' date columns from client tools as soon as you've connected them with your date table.
hth, Imke

Details:
The Ultimate Date Table « PowerPivotPro
Calendar Tables: Not Just for Formulas. Use Them on Your Pivots Too! « PowerPivotPro

 

[seancsn]

Once again Imke, your advice worked a treat! Thank you so much.