cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
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
Highlighted
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

View solution in original post

4 REPLIES 4
Highlighted
Super User V
Super User V

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]))


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
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

Highlighted
Super User V
Super User V

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Highlighted
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

View solution in original post

Helpful resources

Announcements
Ignite

Microsoft Ignite

This will be a conference that you do not want to miss!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Don't miss the Power BI Dev Camp this week!

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Kudoed Authors