cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
jfreyha Frequent Visitor
Frequent Visitor

Date table added columns (ex: monthno) doesn't work as axis for Sameperiodlastyear measures

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!

1 ACCEPTED SOLUTION

Accepted Solutions
jfreyha Frequent Visitor
Frequent Visitor

Re: Date table added columns (ex: monthno) doesn't work as axis for Sameperiodlastyear measures

I found out what's my problem. my date table was not marked as Date Table. after doing so. things worked seemlessly

4 REPLIES 4
Super User
Super User

Re: Date table added columns (ex: monthno) doesn't work as axis for Sameperiodlastyear measures

Hi,

 

Your formula should be this

 

=CALCULATE([the measure],SAMEPERIODLASTYEAR(Calendar[Date]))

jfreyha Frequent Visitor
Frequent Visitor

Re: Date table added columns (ex: monthno) doesn't work as axis for Sameperiodlastyear measures

why so? 
I tried it and I get my LYMeasure having values same as my Measure. this way it is not working at all

Super User
Super User

Re: Date table added columns (ex: monthno) doesn't work as axis for Sameperiodlastyear measures

Hi,

 

To get further help, i will need to see your file.  In that file, also show your expected result.

jfreyha Frequent Visitor
Frequent Visitor

Re: Date table added columns (ex: monthno) doesn't work as axis for Sameperiodlastyear measures

I found out what's my problem. my date table was not marked as Date Table. after doing so. things worked seemlessly