Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jfreyha
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
jfreyha
Frequent Visitor

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
jfreyha
Frequent Visitor

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

Ashish_Mathur
Super User
Super User

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/

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

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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.