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

speedramps — Tue, 17 Mar 2020 16:57:00 GMT

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

)

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

Greg_Deckler — Tue, 17 Mar 2020 17:09:03 GMT

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

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

speedramps — Tue, 17 Mar 2020 17:29:25 GMT

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.

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

Anonymous — Sun, 22 Mar 2020 13:59:51 GMT

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

Best
D

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

Anonymous — Sun, 22 Mar 2020 14:10:48 GMT

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

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

speedramps — Sun, 22 Mar 2020 14:34:28 GMT

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.

topic Re: How to create a slicer with MTD, YTD and Month YYYY options in desired sequence? in DAX Commands and Tips

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

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

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

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

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

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