Sorting custom Period calculated table

[BBConsultancy]

Hi All,

I created a calculated table that defines custom periods. I use this in my report, so users can select the custom periods in a slicer. I run into an issue when I want to make timeline visuals. In a timeline visual my X-axis needs to be dynamic. When a user selects Period = Last week, the x-axis needs to be in days of week. And when the user selects this year, the axis needs to be in months of year.

I partly solved this by adding the X-as column in my calculated table. But I cannot find a way to get the sorting fixed. I did add a sorting column at the bottom of the code, but power bi desktop won't let me sort x-axis on column "sortering". Which I understand, because there's no one to one mapping.

An alternative would be to show X-axis key on the X-axis of the visual, but I don't like the look of that.

If anyone can tell me how I can fix this sorting issue, you'd be my hero!

DAX Code:

Period Selection =
VAR _Today = TODAY()
VAR _TodayYear = YEAR(_Today)
VAR _TodayMonth = MONTH(_Today)
VAR _TodayLastWeek = _Today - 7
VAR _YearLastWeek = YEAR(_TodayLastWeek)
VAR _DayOfYear = DATEDIFF(DATE(_TodayYear, 1, 1), _Today, DAY) + 1

VAR _OptionToday =
SELECTCOLUMNS(
FILTER(
'Date',
Date[Date] = _Today
),
"Date", 'Date'[Date],
"Period", "Today",
"X-axis", Date[ShortDayName],
"Order", 1
)

VAR _Yesterday =
SELECTCOLUMNS(
FILTER(
'Date',
Date[Date] = _Today - 1
),
"Date", 'Date'[Date],
"Period", "Yesterday",
"X-axis", Date[ShortDayName],
"Order", 2
)

VAR _Last7Days =
SELECTCOLUMNS(
FILTER(
'Date',
Date[Date] <= _Today &&
Date[Date] >= _Today - 6
),
"Date", 'Date'[Date],
"Period", "Last 7 days",
"X-axis", Date[ShortDayName],
"Order", 3
)

VAR _ThisWeek =
SELECTCOLUMNS(
FILTER(
'Date',
Date[WeekNumber] = WEEKNUM(_Today) &&
Date[DayOfWeek] <= WEEKDAY(_Today, 2) &&
Date[Year] = _TodayYear
),
"Date", 'Date'[Date],
"Period", "This week",
"X-axis", Date[ShortDayName],
"Order", 4
)

VAR _LastWeek =
SELECTCOLUMNS(
FILTER(
'Date',
Date[WeekNumber] = WEEKNUM(_TodayLastWeek) &&
Date[Year] = _YearLastWeek
),
"Date", 'Date'[Date],
"Period", "Last week",
"X-axis", Date[ShortDayName],
"Order", 5
)

VAR _Last30Days =
SELECTCOLUMNS(
FILTER(
'Date',
Date[Date] <= _Today &&
Date[Date] >= _Today - 29
),
"Date", 'Date'[Date],
"Period", "Last 30 days",
"X-axis", Date[DayOfMonth],
"Order", 6
)

VAR _ThisMonth =
SELECTCOLUMNS(
FILTER(
'Date',
Date[MonthNumber] = _TodayMonth &&
Date[DayOfMonth] <= DAY(_Today) &&
Date[Year] = _TodayYear
),
"Date", 'Date'[Date],
"Period", "This month",
"X-axis", Date[DayOfMonth],
"Order", 7
)

VAR _LastMonth =
SELECTCOLUMNS(
FILTER(
'Date',
Date[MonthNumber] = MONTH(EDATE(_Today, -1)) &&
Date[Year] = YEAR(EDATE(_Today, -1))
),
"Date", 'Date'[Date],
"Period", "Last month",
"X-axis", Date[DayOfMonth],
"Order", 8
)

VAR _Last3Months =
SELECTCOLUMNS(
FILTER(
'Date',
Date[Date] <= _Today &&
Date[Date] >= EDATE(_Today, - 3)
),
"Date", 'Date'[Date],
"Period", "Last 3 months",
"X-axis", Date[Week],
"Order", 9
)

VAR _Last6Months =
SELECTCOLUMNS(
FILTER(
'Date',
Date[Date] <= _Today &&
Date[Date] >= EDATE(_Today, - 6)
),
"Date", 'Date'[Date],
"Period", "Last 6 months",
"X-axis", Date[Week],
"Order", 10
)

VAR _ThisYear =
SELECTCOLUMNS(
FILTER(
'Date',
Date[Year] = _TodayYear &&
Date[DayOfYear] <= _DayOfYear
),
"Date", 'Date'[Date],
"Period", "This year",
"X-axis", Date[MonthOfYear],
"Order", 11
)

VAR _LastYear =
SELECTCOLUMNS(
FILTER(
'Date',
Date[Year] = _TodayYear - 1
),
"Date", 'Date'[Date],
"Period", "Last year",
"X-axis", Date[MonthOfYear],
"Order", 12
)

VAR _Other =
SELECTCOLUMNS(
'Date',
"Date", 'Date'[Date],
"Period", "Other",
"X-axis", Date[QuarterOfYear],
"Order", 13
)

VAR _Combined =
UNION(_OptionToday, _Yesterday, _Last7Days, _ThisWeek, _LastWeek, _Last30Days, _ThisMonth, _LastMonth, _Last3Months, _Last6Months, _LastYear, _ThisYear, _Other)

VAR _WithXAxisKey =
ADDCOLUMNS(
_Combined,
"X-axis key",
[Period] & " - " & [X-axis]
)

VAR _WithSorting =
ADDCOLUMNS(
_WithXAxisKey,
"Sorting",
RANKX(
FILTER(
_WithXAxisKey,
[Order] = EARLIER([Order])
),
[Date],
,ASC,Dense
)
)

RETURN
_WithSorting

[v-sdhruv]

Hi @BBConsultancy ,

Thank you for the update. If you need any assistance, feel free to reach out and we would be happy to assist you.
Thank you for using Microsoft Community Forum.

[v-sdhruv]

Hi @BBConsultancy ,

Just wanted to check if you got a chance to review the suggestion provided and whether that helped you resolve your query?
Thank You @Ritaf1983  for your insights into the query

[BBConsultancy]

Not really unfortunately. Some period buckets need to have the same display names. But the response of @Ritaf1983 confirmed my thoughts that there is no possible way to make it work in my current setup.

[BBConsultancy]

Thank you for your quick reply. Here's a sample pbix: https://we.tl/t-D5fA4Vv5mp

The slicer controls what you see in the column chart and I want the sorting of the X-axis to be correct for each slicer selection.

[Ritaf1983]

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

[BBConsultancy]

So your solution comes down to making sure each period bucket has unique display names? Just to be sure I get it right. If that's the case the solution won't be optimal, because I rather have some period bucket show the same display name on the X-axis.

[Ritaf1983]

Hi @BBConsultancy 
If you want the sorting to work correctly while using a single slicer and a single visual, there’s no other option — each period bucket must have a unique display name .
The only alternative is to avoid the dynamic visual and instead use bookmarks / separate pages, each already sorted and arranged according to the desired option.

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

[Ritaf1983]

Hi @BBConsultancy 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523