Date Hierarchy Showing Year - Measure & Variance Measure Problem

[Anonymous]

Hi guys. Long time listener, first time caller.

My problem is that I can't get the date hierarchy with just the year showing to sort in descending order, and when I try to switch the header to be a text column of the year it breaks my measures.  I have tried sorting the date hierarchy column and adding a key to sort it properly and neither worked.

For my data set, I'm showing current year and prior year data for a measure that is built to only show the MTD and PYMTD.  Here's the formula:

MTD =

      var Ldate = Max('Table2'[Date])

      Return(

          CALCULATE(

               SUM(Table1[PostedTotalAMT]), 'Table1'[Date].[MonthNo]= MONTH(Ldate)))

I created this formula based on the solution in this post: Link

Note that the Ldate variable and date reference in the formula use different tables.  That needs to be the case for this to work.

I have also created a measure that basically uses the same formula, but subtracts the 2018 MTD data from the 2019 MTD data.  I have created a table using both of these measures, with the column headers being the date hierarchy (Table1) with everything taken out besides the year.  Power BI seems to be preventing me from having these columns go in descending order:

When I try changing this to be a text column with the current and prior year and get it correctly sorted, the variance formula bugs out and won't calculate correctly:

Does anyone know a way that I could make this work? Thank you in advance...

[Cmcmahan]

I'm curious about why adding a key to sort it by didn't work. That's what I'd usually recommend for this problem.  Something simple like (0-YEAR([date])) would get you the order you want. How/why did it fail to meet your needs?