Sorting custom Period calculated table

Hi BBConsultancy 

Since your sample file isn’t in English and it’s hard to work on directly, I’m demonstrating the approach on my own model.

Why sorting breaks here
In your Date table, each grain (day/week/month) has its own numeric sort column, so visuals can sort chronologically.

In the parameter output here everything lands in one column, so each option (e.g., “Last 3 months”, “Last month”, “Last week”) needs its own numeric series to avoid collisions when all options are shown together.

What I did
Built a small helper table that returns dates for three buckets: Last 3 months, Last month, Last week.

Added a Display text for the x-axis (different format per bucket).

Added a single SortKey numeric column that encodes order as:

Order = 1 (Last 3 months): 1000 * 100,000,000 + YYYYMMDD

Order = 2 (Last month): 10,000 * 100,000,000 + day-of-month

Order = 3 (Last week): 100,000 * 100,000,000 + ISO weekday (Mon=1..Sun=7)

Connected the chart to the model using one measure with TREATAS, so your existing measures (e.g., [Total Sales]) calculate only for the dates in each bucket.

DAX (calculated table)

// Helper table for displaying 3 recent periods with per-bucket sorting
// Replace 'Calendar'[Date] with your date table/column if needed.
Periods_Display =
VAR _Anchor =
CALCULATE ( MAX ( 'Calendar'[Date] ), ALL ( 'Calendar' ) ) // last date in the calendar

// Last 3 months: from the start of month (-3) up to _Anchor
VAR _3M_Start = EOMONTH ( _Anchor, -3 ) + 1
VAR _3M_End = _Anchor

// Last month: previous full calendar month
VAR _LM_Start = DATE ( YEAR ( EDATE ( _Anchor, -1 ) ), MONTH ( EDATE ( _Anchor, -1 ) ), 1 )
VAR _LM_End = EOMONTH ( _Anchor, -1 )

// Last week (ISO Mon–Sun): the week prior to _Anchor
VAR _RefPrevWeek = _Anchor - 7
VAR _LW_Start = _RefPrevWeek - WEEKDAY ( _RefPrevWeek, 2 ) + 1 // Monday
VAR _LW_End = _LW_Start + 6 // Sunday

// Build per-period tables with a text Display and an Order bucket
VAR _Last3Months =
SELECTCOLUMNS (
FILTER ( 'Calendar', 'Calendar'[Date] >= _3M_Start && 'Calendar'[Date] <= _3M_End ),
"Date", 'Calendar'[Date],
"Period", "Last 3 months",
"Order", 1,
"Display", FORMAT ( 'Calendar'[Date], "dd/MM" )
)
VAR _LastMonth =
SELECTCOLUMNS (
FILTER ( 'Calendar', 'Calendar'[Date] >= _LM_Start && 'Calendar'[Date] <= _LM_End ),
"Date", 'Calendar'[Date],
"Period", "Last month",
"Order", 2,
"Display", FORMAT ( 'Calendar'[Date], "dd" )
)
VAR _LastWeek =
SELECTCOLUMNS (
FILTER ( 'Calendar', 'Calendar'[Date] >= _LW_Start && 'Calendar'[Date] <= _LW_End ),
"Date", 'Calendar'[Date],
"Period", "Last week",
"Order", 3,
"Display", FORMAT ( 'Calendar'[Date], "dddd" )
)

// Union and add a SortKey that won't collide across buckets
VAR _U = UNION ( _Last3Months, _LastMonth, _LastWeek )

RETURN
ADDCOLUMNS (
_U,
"SortKey",
VAR o = [Order]
VAR d = [Date]
VAR prefix =
SWITCH ( o, 1, 1000, 2, 10000, 3, 100000, 0 )
VAR suffix =
SWITCH (
o,
1, YEAR ( d ) * 10000 + MONTH ( d ) * 100 + DAY ( d ), // YYYYMMDD
2, DAY ( d ), // 1..31
3, WEEKDAY ( d, 2 ), // Mon=1..Sun=7
BLANK ()
)
RETURN prefix * 100000000 + suffix
)

DAX (measure – plug your existing measure via TREATAS) 

// Replace [Total Sales] with your base measure name
Total Sales (Periods) =
VAR DatesInScope = VALUES ( 'Periods_Display'[Date] )
VAR AllShownDates =
CALCULATETABLE ( VALUES ( 'Periods_Display'[Date] ), ALLSELECTED ( 'Periods_Display'[Display] ) )
RETURN
IF (
ISINSCOPE ( 'Periods_Display'[Display] ),
CALCULATE (
[Total Sales],
REMOVEFILTERS ( 'Calendar'[Date] ),
TREATAS ( DatesInScope, 'Calendar'[Date] )
),
CALCULATE (
[Total Sales],
REMOVEFILTERS ( 'Calendar'[Date] ),
TREATAS ( AllShownDates, 'Calendar'[Date] )
)
)

Sort the colum that you want to use as X axis by sortkey:

RESULT:

The pbix with the example is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly