Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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!
Solved! Go to Solution.
I found out what's my problem. my date table was not marked as Date Table. after doing so. things worked seemlessly
I found out what's my problem. my date table was not marked as Date Table. after doing so. things worked seemlessly
Hi,
Your formula should be this
=CALCULATE([the measure],SAMEPERIODLASTYEAR(Calendar[Date]))
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.
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |