Hi,
I have a calendar table connected to my data. in this calendar table, the primary key is the calendar date. I have added some custom columns such as Fiscal Date, Fiscal Quarter, Fiscal Year, Month Name...etc. I have also made some last year compare measures: very simple on paper: Calculate([the measure] , SAMEPERIODLASTYEAR('Date'[FiscalDate].[Date])
PS: I have also tried the quick measure that used Dateadd instead of sameperiodlastyear.
when I graph this new measure with the Fiscal Date Hierarchy as an axis. it works beautifully. but you see I don't want to use Fiscal Date hierarchy as axis: the defaultmonth names are not right, also it is not intuitive for Q&A. I want to use my other custom columns such as FY and and FQ and FM...etc, who are all in the calendar table. but they don't work! they work with the normal measures, but not with the lastyearcompares!
I have been tearing my hair for the past 3 hours over this. seems if I put the year from FiscalDate hierarchy and then add the custom column Quarter under it. it works! it works as long as I use a Date Hierarchy in the axis. but I don't want to, and I shouldn't need to.
Needless to say all the custom columns in my calendar table including the fiscaldate, are derived from a primary key CalendarDate column.
HELP!
