SAMEPERIODLASTYEAR - Charting with Month End Values

[seabrew]

I have a aggregated month end value to show along with the aggregated month end of the prior year's month value. These are not cumulative values, we need to show what the value is at the end of each month with that same value 12 months ago. 

I have the calendar table setup correctly. 

The issue I am having is that I can only get the SAMEPERIODLASTYEAR value to work if I am using the default date hierarchy on my datekey calendar field.

If I remove year and/or quarter the prior value is the same as the current value which I understand is likely because DAX is evaulating the period without a year or quarter context. The only way I can get the prior value to work at the month level on a bar chart is to drill down from year -> month -> quarter. The problem with this is that I need to see more than three months at a time. 

How do I display several month values in this formula without being locked into three months in a particular quarter? 

Here are my measures

TotalValues = SUM(AccountSecurityMonthEndValues[Value])

Prior Year Values = CALCULATE([TotalValues], SAMEPERIODLASTYEAR('Calendar'[DateKey]))

This is what it looks like when I drill down to month with the date hierarchy intact. Again, this is correct, but I need to show more months than those within a current quarter. 

[Vvelarde · Accepted solution]

@seabrew

hi, you can go to next level with the icon with double arrow and Concatenate to X Axis to OFF

[seabrew]

@Vvelarde - Thank you! Expand all worked!

One more question if you don't mind. How do you change the date format of the X axis from being grouped by Year/Quarter/Month to just showing the MM/YYYY? Changing the format of the DateKey field in my calendar table doesn't do anything. 

[Vvelarde]

@seabrew

if you are using the Date Herarchy i think the most closer to this is removing the Quarter of the Herarchy and Concatenate is ON.

[seabrew]

Thank you! This is much closer to what I was looking for.