SAMEPERIODLASTYEAR Function

[Chers]

Hello... I am in a middle of powerpivot where i have put a calculated field using SAMEPERIODLASTYEAR.

Total Rev LY (AED):=CALCULATE([Total Rev (AED)],SAMEPERIODLASTYEAR(fPOSData[MonthYear]))

It worked and it gave me a figure as a result.

But when i am dropping it in the pivot table, it says it could not add the field to the Pivot Table because the formula is invalid.

My month-year column in my fact table is connected to the dimension table of dates.

How do i fix this? Thanks a million.

 

[theBardd]

Does the date table have every contiguous date? Is it marked as a date table?

 

[Chers]

It is marked as the date table. My dates column has this as first column:

MMMYY</SPAN></SPAN>

01/03/2013 00:00:00</SPAN></SPAN>

01/04/2013 00:00:00</SPAN></SPAN>

01/05/2013 00:00:00</SPAN></SPAN>

01/06/2013 00:00:00</SPAN></SPAN>

01/07/2013 00:00:00</SPAN></SPAN>

01/08/2013 00:00:00</SPAN></SPAN>

01/09/2013 00:00:00</SPAN></SPAN>

01/10/2013 00:00:00</SPAN></SPAN>

01/11/2013 00:00:00</SPAN></SPAN>

01/12/2013 00:00:00</SPAN></SPAN>

<TBODY>
</TBODY>

I have changed the formula to:
Total Rev LY:=CALCULATE([Total Rev],SAMEPERIODLASTYEAR(dDate[MMMYY]))


This time the result is the same as my Total Rev... and it still doesn't drop into my pivot table

 

[Chers]

For some reason i managed to fix it by cleaning up my Date table. thanks.