How to create a slicer with MTD, YTD and Month YYYY options in desired sequence?

[speedramps]

I want to create a slicer with a list of months (month year) sorted by most recent first.
However, before the list of months I want MTD, Last Month, YTD and Last Year options.

For example:-

MTD
Last Month
YTD (excluding last month)
Last Year
March2020
February 2020
January 2020
December 2019
November 2019
October 2019

I have created the following DAX table with a bidirectional relationship to DimDate.
It works a treat but I don’t know how to sort the slicer into the above desired order.

I tried adding a “sort key” and then using the Modelling ‘sort by column’ option on the Selection field, but I can’t because the sort key must be unique.

Any suggestions would be appreciated, thank you

List Of  Periods =

// get start & end date for MTD, Last month, YTD and Last Year

VAR MTDStart = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=0))

VAR MTDEnd = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=0))

VAR LastMonthStart = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-1))

VAR LastMonthEnd = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-1))

VAR YTDStart = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[YearOffset]=0))

VAR YTDEnd = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[YearOffset]=0))

VAR LastYearStart = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[YearOffset]=-1))

VAR LastYearEnd = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[YearOffset]=-1))

// get start & end date and name for each month

VAR M1Start = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-1))

VAR M1End = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-1))

VAR M1Name = CALCULATE(MAX('DimDate'[LongMonthYear]),FILTER('DimDate','DimDate'[MonthOffset]=-1))

VAR M2Start = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-2))

VAR M2End = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-2))

VAR M2Name = CALCULATE(MAX('DimDate'[LongMonthYear]),FILTER('DimDate','DimDate'[MonthOffset]=-2))

VAR M3Start = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-3))

VAR M3End = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-3))

VAR M3Name = CALCULATE(MAX('DimDate'[LongMonthYear]),FILTER('DimDate','DimDate'[MonthOffset]=-3))

VAR M4Start = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-4))

VAR M4End = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-4))

VAR M4Name = CALCULATE(MAX('DimDate'[LongMonthYear]),FILTER('DimDate','DimDate'[MonthOffset]=-4))

VAR M5Start = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-5))

VAR M5End = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-5))

VAR M5Name = CALCULATE(MAX('DimDate'[LongMonthYear]),FILTER('DimDate','DimDate'[MonthOffset]=-5))

VAR M6Start = CALCULATE(MIN('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-6))

VAR M6End = CALCULATE(MAX('DimDate'[Date]),FILTER('DimDate','DimDate'[MonthOffset]=-6))

VAR M6Name = CALCULATE(MAX('DimDate'[LongMonthYear]),FILTER('DimDate','DimDate'[MonthOffset]=-6))

RETURN

// Create and join list of dates for each period

UNION (

ADDCOLUMNS(

    CALENDAR(MTDStart,MTDEnd),

    "Sort key",1,

      "Selection", "MTD"

),

ADDCOLUMNS(

    CALENDAR(LastMonthStart,LastMonthEnd),

    "Sort key",2,

    "Selection", "Last month"

),

ADDCOLUMNS(

    CALENDAR(YTDStart,YTDEnd),

    "Sort key",4,

    "Selection", "YTD"

),

ADDCOLUMNS(

    CALENDAR(LastYearStart,LastYearEnd),

    "Sort key",5,

    "Selection", "Last year"

),

ADDCOLUMNS(

    CALENDAR(M1Start,M1End),

    "Sort key",6,

    "Selection", M1Name

),

ADDCOLUMNS(

    CALENDAR(M2Start,M2End),

    "Sort key",7,

    "Selection", M2Name

),

ADDCOLUMNS(

    CALENDAR(M3Start,M3End),

    "Sort key",8,

    "Selection", M3Name

),

ADDCOLUMNS(

    CALENDAR(M4Start,M4End),

    "Sort key",9,

    "Selection", M4Name

)

)

[Anonymous]

I don't believe that you cannot sort Selection by Sort Key. The values in both columns are in 1-1 correspondence and therefore you CAN order Selection by Sort Key.

If PBI tells you it can't, then it means they are NOT in 1-1 correspondence. Check you code.

Best
D

[speedramps]

Many thanks for all the replies.
I decided to use Power Query M instead of DAX. It works a treat. Consider the matter closed. Thank you.

[Anonymous]

Model tables in PBI are refreshed ONLY when data is refreshed from its source. There is no other way.

Best
D

[Greg_Deckler]

Create a table with an Enter Data query and put in your Sort manually in there along with your slicer selections. Use Sort By.

[speedramps]

Thank you Greg for the 'Enter Data' suggestion.
However, that wont't that create a static table?  I want my slicer to be dynamic and refresh each month.

I could use Power Query M but I thought a DAX table would be easier, and it was easy upto the point I got stuck with the sort.